Arzozeus Arzozeus - 1 month ago 17
SQL Question

SQL Unpivot table

I am facing problem on unpivot sql statement, below is the table how its look like:

ID A0001 A0002 A0003
== =========== ========== ==========
S1 100 200 300
S2 321 451 234
S3 0 111 222


I want to pivot A0001,A0002 and A0003. Create 3 more column for HEADER,SEQUENCE AND DATA.
Below is my expected table to become like this:

ID HEADER SEQUENCE DATA
== ========== =========== =======
S1 A0001 1 100
S1 A0001 2 200
S1 A0001 3 300
S2 A0002 1 321
S2 A0002 2 451
S2 A0002 3 234
S3 A0003 1 111
S3 A0003 2 222


Below is the sql statement I have try:
SELECT ID,DATA FROM
(SELECT ID,A0001,A0002,A0003 FROM STG.TABLE_A)
UNPIVOT
(DATA FOR B IN (A0001,A0002,A0003)) C

The SQL I write only allow to show the data after pivot, for HEADER and SEQUENCE field I have no idea how to write

Secondly, I also like to filter out if any pivot column is zero will be filter out. Example, ID = S3, A0001 is 0,filter the zero and only get other fields which is greater than zero

Answer

You can have this condition after appling unpivot as below -

SELECT ID, DATA, header
  FROM (SELECT ID, A0001, A0002, A0003 FROM STG.TABLE_A) 
        UNPIVOT(DATA FOR header IN (A0001, A0002, A0003)) C
 where data <> 0

You can either use the unvipot function or you can simply use union also in this case as below -

   select id, header, sequence, data
    from (select @i := if(@lastid != id, 1, $i + 1) as sequence,
           @lastid := id,
           id,
           header,
           data
      from (

            select ID, 'A0001' as Header, A0001 as DATA
              from your_table_name
             where A0001 <> 0
            union all
            select ID, 'A0002' as Header, A0002 as DATA
              from your_table_name
             where A0002 <> 0
            union all
            select ID, 'A0003' as Header, A0003 as DATA
              from your_table_name
             where A0003 <> 0
            )t_1
            ORDER BY ID, DATA
    ) t_2
Comments