This question already has an answer here:
I have a few items in the database in the form as below:
ID|ColName|ColValue
-------------------
1 |A |1testa
1 |B |1testb
1 |C |1testc
1 |D |1testd
2 |A |2testa
2 |D |2testd
I need data in the form below:
ID| A | B | C | D
1 | 1testa | 1testb | 1testc | 1testd
2 | 2testa | NULL | NULL | 2testd
I have tried using PIVOT in T-SQL but it takes aggregate function as argument, which I do not want to supply.
How can I achieve this.
Best How To :
The following code:
DECLARE @DataSource TABLE
(
[ID] TINYINT
,[ColName] CHAR(1)
,[ColValue] VARCHAR(12)
);
INSERT INTO @DataSource ([ID], [ColName], [ColValue])
VALUES (1, 'A', '1testa')
,(1, 'B', '1testb')
,(1, 'C', '1testc')
,(1, 'D', '1testd')
,(2, 'A', '2testa')
,(2, 'D', '2testd');
SELECT *
FROM @DataSource
PIVOT
(
MAX([ColValue]) FOR [ColName] IN ([A], [B], [C], [D])
) PVT
is going to give you this:
Note, that when you are using the PIVOT/UNPIVOT clauses, you need to specified the columns (in your case A, B, C, D). If you do not want to hard-coded then, you need to build a dynamic pivot using - this can be done building the T-SQL statement in a string and executing it using sp_executesql.
