SQL Pivot multiple columns without forcing aggregate

Question

I need to pivot out some denormalized data but it repeats so I need it to pivot out the columns and then return multiple rows.

I have a table like this

INSERT #TheTable
VALUES 
        ('StockCode'    ,'a'),
        ('Warehouse'    ,'b'),
        ('TrnYear'  ,'c'),
        ('TrnMonth' ,'d'),
        ('EntryDate'    ,'e'),
        ('TrnTime'  ,'f'),
        ('StockCode'    ,'1'),
        ('Warehouse'    ,'2'),
        ('TrnYear'  ,'3'),
        ('TrnMonth' ,'4'),
        ('EntryDate'    ,'5'),
        ('TrnTime'  ,'6')

But when I pivot it only returns one row:

SELECT  StockCode,
        Warehouse,
        TrnYear,
        TrnMonth,
        TrnTime,
        EntryDate        
FROM    #TheTable AS src 
PIVOT   (MAX(column_value)
         FOR COLUMN_NAME in ([TrnYear], [TrnMonth], [EntryDate], [TrnTime], [StockCode], [Warehouse])) AS piv

Result:

StockCode   Warehouse   TrnYear TrnMonth    TrnTime EntryDate   
-------------------------------------------------------------   
a           b           c       d           f       e

But I need it to return

StockCode   Warehouse   TrnYear TrnMonth    TrnTime EntryDate   
-------------------------------------------------------------   
a           b           c       d           f       e           
1           2           3       4           5       6

Without a column that defines group it does not make sense and tables are not ordered. [db<>fiddle demo](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e9712e09026e72bd9f1d846a3bded58d) Why we have `a,b,c,d,f,e` and not `a,2,c,4,f,g` in the first row? — Lukasz Szozda, Oct 20 '20 at 19:56

score 2 · Answer 1 · answered Oct 20 '20 at 20:04

You can use window functions first, then conditional aggregation:

select 
    max(case when column_name = 'StockCode' then column_value end) StockCode,
    max(case when column_name = 'Warehouse' then column_value end) Warehouse,
    max(case when column_name = 'TrnYear'   then column_value end) TrnYear,
    max(case when column_name = 'TrnMonth'  then column_value end) TrnMonth,
    max(case when column_name = 'TrnTime'   then column_value end) TrnTime,
    max(case when column_name = 'EntryDate' then column_value end) EntryDate
from (
    select t.*,
        row_number() over(partition by column_name order by column_value) rn
    from #TheTable t
) t
group by rn

Thanks. That would work but I need it to be dynamic. I've just figured it out so I'll post the solution below and still vote your solution up — Evan Barke, Oct 20 '20 at 20:06

Barbaros Özhan · Answer 2 · 2020-10-20T20:44:08.850

You can use ROW_NUMBER() Analytic function :

SELECT *
  FROM
 (
  SELECT column_name, column_value,
         ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_value) AS rn
    FROM #TheTable 
 ) q
PIVOT   
 ( MAX(column_value)
   FOR column_name in ([TrnYear], [TrnMonth], [EntryDate], [TrnTime], [StockCode], [Warehouse])
 ) AS piv

or more dynamically, use :

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

SET @cols = ( SELECT STRING_AGG(column_name,',') 
                FROM (SELECT DISTINCT column_name 
                        FROM [#TheTable] ) q );

SET  @query = 
 N'SELECT *  
     FROM
     (
      SELECT column_name, column_value,
             ROW_NUMBER() OVER 
            (PARTITION BY [column_name] ORDER BY [column_value]) AS rn
        FROM [#TheTable]
      ) f
    PIVOT 
    (
     MAX([column_value]) FOR [column_name] IN (' + @cols + N')
    ) AS piv '

EXEC sp_executesql @query;

Demo

score 0 · Accepted Answer · answered Oct 20 '20 at 20:08

The problem was that I needed a row_number per every group of column names. So the below worked

SELECT DISTINCT TrnYear,
                TrnMonth,
                EntryDate,
                TrnTime,
                StockCode,
                Warehouse
FROM
  (SELECT  (ROW_NUMBER() OVER (ORDER BY dw_view_change_event_nr) - 1) / 6 + 1 AS rn,
          COLUMN_NAME ,
         column_value
   FROM
     #TheTable AS tmp) AS src PIVOT (MAX(column_value)
                                                          FOR COLUMN_NAME in ([TrnYear], [TrnMonth], [EntryDate], [TrnTime], [StockCode], [Warehouse])) AS piv

SQL Pivot multiple columns without forcing aggregate

3 Answers3