CODEWITHSUNDEEP
sqlsql-server
Jfelix
Jfelix

Reputation: 43

How to make SQL Pivot display more than one row

I was able to display every row in column using pivot however when there are multiple values, it displays only one of the row values. My suspicion lies on the MAX function in the for loop, however, have not been able to find a successful replacement.

I've tried other SQL functions. 

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

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(Provincia)
                      FROM Codigos_Postales
                      GROUP BY Provincia
                      ORDER BY Provincia
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @query = N'SELECT Poblacion,' + @cols + N' from
            (
                select * from Codigos_Postales
            ) x
            pivot
            (
                MAX(Codigo_Postal)
                for Provincia in (' + @cols + N')
            ) p ORDER BY Poblacion ASC'

EXEC sp_executesql @query;

Table I'm trying to pivot:

original table

Result:

table result

Expected Result:

expected table result

Upvotes: 2

Views: 40

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

If I understand corerctly, adding ROW_NUMBER to your source data will do the trick. The sample PIVOT script will be as below. Just ignore the RN column from your final output, that's it.

SET @query = 
N'SELECT Poblacion,' + @cols + N' from
(
    SELECT
    ROW_NUMBER() OVER (ORDER BY Provincia) RN,
    *  
    FROM Codigos_Postales
) x
PIVOT
(
    MAX(Codigo_Postal)
    FOR Provincia IN (' + @cols + N')
) p ORDER BY Poblacion ASC'

Upvotes: 3

Related Questions