I've just started to get into SQL Server deeper and I have a problem. I have to transform a row into a column, but I can't figure it out.
The row looks like this:
Columns: T1 T2 T3 .........T20
Values: 1 0 9 ......... 15
I want to receive something like this:
Col Val
________
T1 1
T2 0
T3 9
........
T20 15
I know i have to use a pivot, i have read about this, but can't figure it out
You have to use UNPIVOT table operator for this, like this:
SELECT col, val
FROM Tablename AS t
UNPIVOT
(
Val
FOR Col IN (T1, T2, ..., T20)
) AS u;
If you want to do this dynamically for any number of columns, without the need to write them manually, the only way I can think of is by reading these columns from the table information_schema.columns to get the list of columns' names of the table. Then use dynamic SQL to compose the statement FOR col IN ... dynamically like this:
DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' +
QUOTENAME(column_name)
FROM information_schema.columns
WHERE table_name = 'tablename'
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 1, '');
SELECT @query = ' SELECT col, val
FROM tablename AS t
UNPIVOT
(
val
FOR col IN ( ' + @cols + ' )
) AS u;';
EXECUTE(@query);
This will give you:
| COL | VAL |
-------------
| T1 | 1 |
| T10 | 15 |
| T11 | 33 |
| T12 | 31 |
| T13 | 12 |
| T14 | 10 |
| T15 | 12 |
| T16 | 9 |
| T17 | 10 |
| T18 | 2 |
| T19 | 40 |
| T2 | 0 |
| T20 | 21 |
| T3 | 9 |
| T4 | 2 |
| T5 | 3 |
| T6 | 10 |
| T7 | 14 |
| T8 | 15 |
| T9 | 20 |
