Union column value into one row

Question

i have a tablet with this structure:

Usr    Key        Value
Peter   Address    78 Street
Peter   Number     123456
Peter   Sport      Rugby
Peter   Document   ABCDE

i would like to have a query with this:

NAME  Address   Number Sport Document
Peter 78 Street 123456 Rugby ABCDE

Who can help me? See image for result

I have no idea how to use server pivot. Please can you help me? — img.simone, May 28 '14 at 13:28

score 1 · Accepted Answer · answered May 28 '14 at 13:40

If you have only one value for each key for each user:

select P.*
from Table1 as t
pivot (
    max(Value)
    for [Key] in ([Address], [Number], [Sport], [Document])
) as P

or

select
    t.Usr,
    max(case when t.[Key] = 'Address' then t.[Value] end) as Address,
    max(case when t.[Key] = 'Number' then t.[Value] end) as Number,
    max(case when t.[Key] = 'Sport' then t.[Value] end) as Sport,
    max(case when t.[Key] = 'Document' then t.[Value] end) as Document
from Table1 as t
group by t.Usr

sql fiddle demo

The ultimate is perfect! Thank you – img.simone May 28 '14 at 13:54 — img.simone, May 28 '14 at 13:54

score 0 · Answer 2 · answered May 28 '14 at 13:41

You should check this link for more info on PIVOT statement. As for your question here's an example how it can be done:

--create temp table and populate it
SELECT 'NAME' AS [KEY], 'Peter' AS VALUE
INTO #tmp
UNION ALL
SELECT 'Address' AS [KEY], '78' AS VALUE
UNION ALL
SELECT 'Number' AS [KEY], '1' AS VALUE
UNION ALL
SELECT 'Sport' AS [KEY], 'Rugby' AS VALUE
UNION ALL
SELECT 'Document' AS [KEY], 'qwretz' AS VALUE

--check what's in here
SELECT [KEY], VALUE FROM #tmp

--pivot
SELECT [NAME], [Address], [Number], [Sport], [DOCUMENT]
FROM
( SELECT [KEY], VALUE FROM #tmp) AS SourceTable
PIVOT
( MAX(VALUE)
FOR [KEY] IN ([NAME], [Address], [Number], [Sport], [DOCUMENT])
) AS PivotTable

Union column value into one row

2 Answers2