CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Pr0no
Pr0no

Reputation: 4099

Pivot a resultset (rows to columns)

Consider the following #table:

guid  field  value
--------------------
123   A      foo
123   B      foobar
123   C      123
234   A      bar;baz
234   B      3464
345   A      foobaz

I need to transpose / pivot this into the following #table2:

guid  A        B        C
---------------------------
123   foo      foobar   123
234   bar;baz  3464     -
345   foobaz   -        -

In other words: the guid needs to stay the table key but all the fields need to be transposed into columns.

Is this possible in SQL Server?

Usually, I would use a server-side (php, python, asp) script to pull #table from the database and transpose the resultset by iterating to the resultset as an array but this is not an option in this case. I need a sql-only solution.

Any help would be greatly appreciated.

Upvotes: 0

Views: 1378

Answers (1)

atom.gregg
atom.gregg

Reputation: 1007

This should pivot your data and will keep your GUID.

select tbl.guid, tbl.[A], tbl.[B], tbl.[C] from (
  select * from (
    select guid, field, value
    from #table
  ) t
  pivot (
    max(value) for field in ([A],[B],[C])
  ) p
) tbl

Creating your columns in preparation for the dynamic SQL:

declare @columns nvarchar(max) = (select stuff((
    select distinct ',[' + t.field + ']'
    from #table t
    for xml path('')
),1,1,''))

Mixing the columns into the dynamic SQL:

declare @sql = N'
select tbl.guid, ' + @columns + ' from (
    select * from (
        select guid, field, value
        from #table
    ) t
    pivot (
        max(value) for field in (' + @columns + ')
    ) p
) tbl'

And execute:

execute (@sql)

Upvotes: 2

Related Questions