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?
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).
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')
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
╔═════╦═════╦═════╦═════╦═════╗
║ 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 ║
╚═════╩═════╩═════╩═════╩═════╝
