Convert column values to row values

Question

  **uid         pid    mail   value**

     1          78     27     Nairobi

     2          78     27     Milimani

     3          78     27     Criminal

     4          78     27     1427932800

I have a DB table above and only need the 'value' column values. I want to have the column values display in rows (not comma separated) for a cross-tab report. my ideal result would be:

        **Nairobi  Milimani  Criminal  1427932800**

The matching 'pid' and 'mail' means that the corresponding 'value' is from a single submission and a change in pid and mail (not captured here) is a new submission!

so how do I write an sql for converting the 'value' column values to row values? any help much appreciated.

'Pivot' has not really helped or i'm probably doing it wrongly.!!

So if someone inserts one additional row, you'll suddenly have 5 columns?!? — jarlh, Jul 08 '15 at 06:38
possible duplicate of [convert column values to rows](http://stackoverflow.com/questions/31278908/convert-column-values-to-rows) — FutbolFan, Jul 09 '15 at 14:19
@polycarproyal Why would you ask the same question twice on a same day? — FutbolFan, Jul 09 '15 at 14:21

Stanislovas Kalašnikovas · Answer 1 · 2015-07-08T06:57:53.077

In SQL-Server you can use PIVOT.

CREATE TABLE #Test
(
    [uid] INT,
    pid INT,
    mail INT,
    value NVARCHAR(60)
)
INSERT INTO #Test VALUES 
(1,          78,     27,     'Nairobi'),
(2,          78,     27,     'Milimani'),
(3,          78,     27,     'Criminal'),
(4,          78,     27,     '1427932800')

This is example of STATIC PIVOT:

SELECT [Nairobi], [Milimani], [Criminal], [1427932800]
FROM (
    SELECT value
    FROM #Test
) x
PIVOT
(
MAX(value)
FOR value in ([Nairobi], [Milimani], [Criminal], [1427932800])
) piv
DROP TABLE #Test

And this is example of DYNAMIC PIVOT:

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(value) 
                    FROM #test
                    GROUP BY value, [pid]
                    ORDER BY [pid]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = N'SELECT ' + @cols + N' from 
             (
                SELECT value
                FROM #test
            ) x
            PIVOT
            (
                MAX(value)
                FOR value IN (' + @cols + N')
            ) p '

EXEC sp_executesql @query;

Convert column values to row values

1 Answers1