CODEWITHSUNDEEP
sqlpostgresqltranspose
mrbf
mrbf

Reputation: 522

How transpose a table using SQL?

I have a table like this:

id P C A B
1 100 3 a1 b1
2 101 3 a2 b2
3 102 3 a3 b3
4 103 3 a4 b4
5 100 4 a5 b5
6 101 4 a6 b6
7 102 4 a7 b7
8 103 4 a8 b8

I want to get a new transposed structure like this:

P _3A _3B _4A _4B
100 a1 b1 a5 b5
101 a2 b2 a6 b6
102 a3 b3 a7 b7
103 a4 b4 a8 b8

As you can see ,new field names have been extracted from C field in the original table. Is there any way to do this using SQL?

Upvotes: 0

Views: 1187

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Postgres has some advanced functionality in terms of arrays and crosstab. However, a database independent way of doing this is by using aggregation:

select t.p,
       max(case when c = 3 then a end) as a3,
       max(case when c = 3 then b end) as b3,
       max(case when c = 4 then a end) as a4,
       max(case when c = 4 then b end) as b4
from atable t
group by t.p;

This will work in both SQLite and Postgres (and just about any other database).

Upvotes: 2

M.Ali
M.Ali

Reputation: 69554

Test Data

DECLARE @TABLE TABLE (id INT, P INT, C INT, A VARCHAR(2), B VARCHAR(2))
INSERT INTO @TABLE VALUES 
(1  ,100 ,3  ,'a1','b1'),
(2  ,101 ,3  ,'a2','b2'),
(3  ,102 ,3  ,'a3','b3'),
(4  ,103 ,3  ,'a4','b4'),
(5  ,100 ,4  ,'a5','b5'),
(6  ,101 ,4  ,'a6','b6'),
(7  ,102 ,4  ,'a7','b7'),
(8  ,103 ,4  ,'a8','b8')

Query

SELECT * FROM 
 (
    SELECT P , Vals , '_' + CAST(C AS VARCHAR(10)) + N  AS Cols
    FROM @TABLE 
        UNPIVOT (Vals FOR N IN (A, B))up
 )A
 PIVOT (MAX(Vals)
        FOR Cols 
        IN ([_3A],[_3B],[_4A],[_4B])
        )p

Result

╔═════╦═════╦═════╦═════╦═════╗
║  P  ║ _3A ║ _3B ║ _4A ║ _4B ║
╠═════╬═════╬═════╬═════╬═════╣
║ 100 ║ a1  ║ b1  ║ a5  ║ b5  ║
║ 101 ║ a2  ║ b2  ║ a6  ║ b6  ║
║ 102 ║ a3  ║ b3  ║ a7  ║ b7  ║
║ 103 ║ a4  ║ b4  ║ a8  ║ b8  ║
╚═════╩═════╩═════╩═════╩═════╝

Upvotes: 2

Related Questions