CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
SQL006
SQL006

Reputation: 492

Pivot the data in SQL Server

I have the following table data

ID        Night_111    NightOT_112   NightLeave_113
----------------------------------------------------
1          8.00         0.00           0.00            
2         16.00         4.00           0.00     
3          8.00         0.00           2.00

I want that table to convert it into this structure:

ID     wageType   hours
---------------------------
 1        111        8
 2        111       16
 2        112        4
 3        111        8
 3        113        2

Please help

Thanks

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

There are several ways, but I advocate using apply:

select v.*
from t cross apply
     (values (id, 111, Night_111), (id, 112, NightOT_112), (id, 113, NightOT_112)
     ) v(ID, wageType, hours)
where v.hours > 0;

You can do something similar using union all or unpivot. However, apply implements something called a lateral join, which is a very powerful operator and useful many other situations.

Upvotes: 2

Related Questions