nonojojo nonojojo - 2 years ago 106
SQL Question

SQL or PIG script to restructure a table to reflect Columns in repeating rows

So I have a table

ID Tag1 Tag2 Tag3
1 a b
2 a b c
3 a

I would like to transform this table to

ID Tag
1 a
1 b
2 a
2 b
2 c
3 a

using PIG latin or SQL?

Answer Source

In SQL you can do this by using UNION ALL

select Id, Tag1 From yourtable Where Tag1 <> ''
Union All
select Id, Tag2 From yourtable Where Tag2 <> ''
Union All
select Id, Tag3 From yourtable Where Tag3 <> ''

If you don't want duplicates then change UNION ALL to UNION. This can done efficiently if your database engine supports unpivot operator

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download