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,

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.