nonojojo nonojojo - 1 month ago 7
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

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

Comments