Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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??

like image 502
Andy L. Avatar asked Nov 30 '17 12:11

Andy L.


2 Answers

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.

like image 90
Gordon Linoff Avatar answered Sep 20 '22 11:09

Gordon Linoff


Another way with XML:

DECLARE @x xml = (SELECT * FROM tbl1 as t FOR XML AUTO, TYPE)

;WITH cte AS (
SELECT  CAST(t.c.query('local-name(.)') as nvarchar(10)) as [name],
        t.c.value('.','int') as [value],
        p.number as [pos]
FROM [master]..spt_values p
CROSS APPLY @x.nodes('/t[position()=sql:column("number")]/@*') as t(c)
WHERE p.[type] = 'p'
)

SELECT  c.[name] as A,
        c.[value] as value1,
        c1.[value] as value2
FROM cte c
INNER JOIN cte c1
    ON c1.[name] = N'c'+c.[name] and c.pos = c1.pos

Output:

A   value1  value2
A1  60      2
A2  30      3
A3  10      5
like image 27
gofr1 Avatar answered Sep 20 '22 11:09

gofr1