NemanjaT NemanjaT - 2 months ago 5
SQL Question

SQL Server: Turning rows into columns

I'm trying to make the following structure

ROW | GROUP | ORDER | COL_NAME | VALUE
---------------------------------------------
1 | 10 | 10 | FIRST_COL | Value1
2 | 10 | 10 | FIRST_COL | Value2
3 | 10 | 10 | FIRST_COL | Value3
4 | 10 | 20 | SECOND_COL| Val1
5 | 10 | 20 | SECOND_COL| Val2
6 | 20 | 10 | THIRD_COL | Opt3
...


into

FIRST_COL | SECOND_COL | THIRD_COL
-----------------------------------------------------
Value1 | Val1 | Opt3
Value2 | Val2 |
Value3 | |


What I currently have:

declare @cols varchar(max),
@query varchar(max)
select @cols = stuff((select ',' + quotename([COL_NAME])
from mt
group by [COL_NAME]
for xml path(''), type).value('.', 'varchar(max)'), 1, 1, '');


set @query = 'select ' + @cols + ' from (
select [COL_NAME], [VALUE]
from mt
) x
pivot (
min([VALUE])
for [COL_NAME] in (' + @cols + ')
) p
';
execute(@query);


The current code only shows the minimum values (since it's set to
min([VALUES])
), so only Value1, Val1 and Opt3 would be shown, but my question here is, how can I modify the code so I get the appropriate table/view?

Thanks!

Answer

I believe you need this:

set @query = 'select ' + @cols + ' from (
    select [COL_NAME], [VALUE], ROW_NUMBER() OVER (PARTITION BY [COL_NAME] ORDER BY (SELECT NULL)) as seqnum
    from mt
) x
pivot (
    min([VALUE])
    for [COL_NAME] in (' + @cols + ')
) p
';

The pivot uses all the columns in the subquery. You just need one to distinguish among the rows with the same column name.

Comments