I have a table like below:
Type PKG_HA_01_ON PKG_HA_03_ON PKG_HA_04_ON PKG_HA_05_ON PKG_HA_06_ON PKG_HA_09_ON duration 18.6694 60 15.1951 56.2068 13.6808 13.8404 counter 5 0 5 11 2 0
The first row is the header. Now, I would like to transpose table into this
Machine Duration Counter PKG_HA_01_ON 18.6694 5 PKG_HA_03_ON 60 0 ...
I have tried unpivot but the result is not desired table.
Thanks in advance,
Advertisement
Answer
I recommend using cross apply
to unpivot and then aggregation:
select machine, max(case when type = 'duration' then val end) as duration, max(case when type = 'counter' then val end) as counter from t cross apply (values ('PKG_HA_01_ON', PKG_HA_01_ON), ('PKG_HA_03_ON', PKG_HA_03_ON), ('PKG_HA_04_ON', PKG_HA_04_ON), ('PKG_HA_05_ON', PKG_HA_05_ON), ('PKG_HA_06_ON', PKG_HA_06_ON), ('PKG_HA_09_ON', PKG_HA_09_ON) ) v(machine, val) group by machine;
I much, much prefer this over pivot
/unpivot
. Why? APPLY
implements a lateral join, which is a powerful construct in SQL (and part of the SQL standard albeit with slightly different syntax). Unpivoting is a nice introduction to this feature.
The pivoting functions are bespoke functions and not part of the standard. They also do not generalize in any way, being designed for a single purpose.