Search code examples
sql-serverpivotunpivot

SQL Server unpivot two columns

I'm trying to pivot a table to get 3 columns

my example table is like :

CREATE TABLE tbl1 (A1 int, cA1 int,A2 int, cA2 int,A3 int, cA3 int)
GO
INSERT INTO tbl1  VALUES (60,2,30,3,10,5);
GO

I am using the query below to get tthe results from two columns:

select A, value from tbl1
unpivot
(
value
for A in ([A1], [A2],[A3])
) un1;

The results are like :

A | value
--+-------
A1|60
A2|30
A3|10

but I want to add and second column with and the results to be like :

A | value1 | value2
--+--------+--------
A1| 60     | 2
A2| 30     | 3
A3| 10     | 5

Any Help??

Solution

  • I would use APPLY:

    select v.*
from tbl1 t cross apply
     (values ('A1', t.A1, t.cA1),
             ('A2', t.A2, t.cA2),
             ('A3', t.A3, t.cA3)
     ) v(A, value1, value2);

    CROSS APPLY implements a lateral join. This is much more powerful than merely unpivoting data, although unpivoting data is one simple way to start learning about lateral joins.