CODEWITHSUNDEEP
sqlsql-servert-sqlpivottranspose
Tùng Huỳnh
Tùng Huỳnh

Reputation: 17

Transpose row to column in SQL Server

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

Answers (2)

Gordon Linoff
Gordon Linoff

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

yildizyasin
yildizyasin

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

Related Questions