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 would also like to filter out if any pivot column is zero will be filter out. Example, ID = S3, A0001 is 0,therefore filter the zero and only get other fields which is greater than zero
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