Retrieving multiple rows dynamically using Pivot in SQL

Question

I wish to add multiple rows for a particular pivot table.

Table name : MasterTable

 FieldName | FieldValue
------------------------
 Field1    | F1value1
 Field2    | F2value1
 Field3    | F3value1
 Field1    | F1value2
 Field2    | F2value2
 Field3    | F3value2

Expected result:

Field1     | Field2    | Field3
---------------------------------
F1value1   | F2value1 | F3value1
F1value2   | F2value2 | F3value2

I tried this code but result me with only one row.

    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)
    select @cols = STUFF((SELECT distinct ',' + QUOTENAME(FieldName) from MasterTable FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
    set @query = 'SELECT  ' + @cols + ' from (select FieldName, FieldValue from MasterTable ) x
                pivot 
                (
                    max(FieldValue)
                    for FieldName in (' + @cols + ')
                ) p '

    execute(@query)

Output:

Field1     | Field2    | Field3
---------------------------------
F1value1   | F2value1 | F3value1

Could someone please help me to have multiple rows using pivote table.

Gordon Linoff · Accepted Answer · 2020-03-26 14:18:18Z

1

For the subquery, use:

(select FieldName, FieldValue,
        row_number() over (partition by FieldName order by FieldName) as seqnum
 from MasterTable
)

pivot will take this into account in the pivoting.

Note: This will guarantee the number of rows, but the ordering of each column is arbitrary. You may want to replace the order by with a reasonable column for ordering.

edited Mar 26, 2020 at 14:18

answered Mar 26, 2020 at 12:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Roshmi Augustine Over a year ago

it worked but shows null values in between ` Field1 | Field2 |Field3 F1value1 NULL NULL F1value2 NULL NULL NULL F2Value2 NULL NULL F2value1 NULL NULL NULL F3Value NULL NULL F3Value2 `

Gordon Linoff Over a year ago

@Roshmi . . . I left out the partition by. The updated version will fix that problem.

Roshmi Augustine Over a year ago

can you please help me once more. I found a problem in displaying the values. it was showing different order. in 1st column 1st row it shows "F1Value1" and then 1st Column 2nd row "F1Value2" but when comes to the 2nd column 1st row it shows "F2Value2" and 2nd column 2nd row shows "F2Value1" . I need all the "Value1" as 1st row and "value2" as 2nd row. for that i have also added a new field to the table "RowOrder" which will specify the order to the pivote table. but i couldnt find a solution.

Gordon Linoff Over a year ago

@Roshmi . . . New questions should be asked as new questions.

Collectives™ on Stack Overflow

Retrieving multiple rows dynamically using Pivot in SQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related