Reputation: 17
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,
Upvotes: 0
Views: 913
Reputation: 1271241
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.
Upvotes: 0
Reputation: 116
Try this:
create table unpivot_raw(
[Type] nvarchar(255)
, PKG_HA_01_ON float null
, PKG_HA_03_ON float null
, PKG_HA_04_ON float null
, PKG_HA_05_ON float null
, PKG_HA_06_ON float null
, PKG_HA_09_ON float null
)
insert into unpivot_raw
select 'duration', 18.6694, 60, 15.1951, 56.2068, 13.6808, 13.8404
union
select 'counter', 5, 0, 5, 11, 2, 0
select
*
from
(
select
[Type]
, vl
, Machine
from
(
select
[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
from unpivot_raw
) p
unpivot
(
vl for Machine in
(
PKG_HA_01_ON
, PKG_HA_03_ON
, PKG_HA_04_ON
, PKG_HA_05_ON
, PKG_HA_06_ON
, PKG_HA_09_ON
)
) unpvt
) base
pivot
(
max(vl) for [Type] in (duration, counter )
) pvt
Upvotes: 3