CODEWITHSUNDEEP
sqlsql-servert-sql
Roshmi Augustine
Roshmi Augustine

Reputation: 65

Retrieving dynamically multiple rows not displaying in properly using Pivot in SQL

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

Table name : MasterTable

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

Expected result:

Field1     | Field2    | Field3  | RowOrder
--------------------------------------------
F1value1   | F2value1 | F3value1 | 0
F1value2   | F2value2 | F3value2 | 1

I tried this code 

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

    select @cols = STUFF((SELECT distinct ',' + QUOTENAME(FieldName) from MasterTable where CatogoryId = @CatogoryId and CompanyId= @CompanyId FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
    set @query = 'SELECT  ' + @cols + ' from (select FieldName, FieldValue,
                        row_number() over (partition by FieldName order by FieldName) as seqnum from MasterTable where 
                    CatogoryId =  ('+  CONVERT(varchar(max),@CatogoryId) +') and CompanyId = ('+  CONVERT(varchar(max),@CompanyId) +') ) x
                pivot 
                (
                    max(FieldValue)
                    for FieldName in (' + @cols + ')
                ) p '

    execute(@query)

I am getting the output as

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

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. Please help

Upvotes: 0

Views: 44

Answers (2)

sacse
sacse

Reputation: 3744

You don't need any seqnum. try the following:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(FieldName) from MasterTable where CatogoryId = @CatogoryId and CompanyId= @CompanyId FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
set @query = 'SELECT  ' + @cols + ', RowOrder
                from 
                (
                    select FieldName, FieldValue, RowOrder
                    from MasterTable where 
                    CatogoryId =  ('+  CONVERT(varchar(max),@CatogoryId) +') and CompanyId = ('+  CONVERT(varchar(max),@CompanyId) +')
                ) x
                pivot 
                (
                    max(FieldValue)
                    for FieldName in (' + @cols + ')
                ) p '

execute(@query)

Example: db<>fiddle

Upvotes: 1

Ilyes
Ilyes

Reputation: 14928

You could use conditional aggregation as

select rn,
       max(case when colname = 'Field1' then colvalue end) col1,
       max(case when colname = 'Field2' then colvalue end) col2,
       max(case when colname = 'Field3' then colvalue end) col3 
from
(
    values
   ('Field1', 'F1value1', 0),
   ('Field2', 'F2value1', 0),
   ('Field3', 'F3value1', 0),
   ('Field1', 'F1value2', 1),
   ('Field2', 'F2value2', 1),
   ('Field3', 'F3value2', 1)
) t(colname, colvalue, rn)
group by rn

Returns:

+----+----------+----------+----------+
| rn |   col1   |   col2   |   col3   |
+----+----------+----------+----------+
|  1 | F1value1 | F2value1 | F3value1 |
|  2 | F1value2 | F2value2 | F3value2 |
+----+----------+----------+----------+

Upvotes: 1

Related Questions