how to do pivot on multiple columns

Question

Hi I have sample data

Declare @table table  
(Name Varchar(10),
 Cnt INT,
 Vol INT,
 Descc VARCHAR(10))

 INSERT INTO @table(Name,cnt,vol,Descc)values ('Mohan',21,8,'Fed')
 INSERT INTO @table(Name,cnt,vol,Descc)values ('Mohan',1,391,'Fed:::')

Data :

Name    Cnt Vol Descc
Mohan   21  8   Fed
Mohan   1   391 Fed:::

How can I get output like this

Name    Cnt1    Vol1    Descc1  cnt2 vol2 Descc2
Mohan   21         8    Fed      1   391  Fed::

script I have followed :

 Select [1],[2] from  (
select NAme,Cnt,vol,DESCc,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY (SELECT NULL))P,'P'+CAST(ROW_NUMBER()OVER(PARTITION BY ID ORDER BY (SELECT NULL))AS VARCHAR)PP from @table )T
PIVOT (MAX(ID) FOR P IN  ([1],[2])) AS P
PIVOT (MAX(ID) FOR PP IN ([P1],[P2])) AS P

I Googled "sql server pivot on multiple columns" and the first result seems to be what you need. https://blogs.msdn.microsoft.com/kenobonn/2009/03/22/pivot-on-two-or-more-fields-in-sql-server/ — Isaac, May 16 '19 at 12:46
@Isaac how can I implement this in my code I'm getting errors — mohan111, May 16 '19 at 13:24
@mohan111 Hii bro check my answer using dynamic sql ,which meets your requirement i hope — Sreenu131, May 16 '19 at 14:06

score 1 · Answer 1 · answered May 16 '19 at 13:43

You can do conditional aggregation :

select Name, max(case when seq = 1 then Cnt end) as cnt1, 
       max(case when seq = 1 then Vol end) as Vol1, 
       max(case when seq = 1 then Descc end) as Descc1,
       max(case when seq = 2 then Cnt end) as cnt2,
       max(case when seq = 2 then Vol end) as Vol2, 
       max(case when seq = 2 then Descc end) as Descc2
from (select t.*, row_number() over (partition by name order by (select 1 )) as seq
      from @table t
     ) t
group by Name;

Here is a db<>fiddle.

Sreenu131 · Accepted Answer · 2019-05-16T14:15:58.007

By Dynamic sql

IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
DROP TABLE #TEMP
DECLARE @table table  
 (
 Name Varchar(10),
 Cnt INT,
 Vol INT,
 Descc VARCHAR(10)
 )

 INSERT INTO @table(Name,cnt,vol,Descc)values ('Mohan',21,8,'Fed')
 INSERT INTO @table(Name,cnt,vol,Descc)values ('Mohan',1,391,'Fed')

;WITH CTE
AS
 (
 SELECT 
    ROW_NUMBER()OVER(ORDER BY Name)  AS Id,*
 FROM @table i
 )
  SELECT ROW_NUMBER()OVER(ORDER BY (SELECT NULL)) AS Seq,
        id,
        Name,
        Data1,
        Data2
        +CAST(id AS VARCHAR(10)) AS ReqCol
   INTO #TEMP
   FROM CTE 
   CROSS APPLY (VALUES ( CAST(CNT AS varchar(10)),'CNT'),
                        (CAST(Vol AS varchar(10)),'vol'),
                        (Descc,'Descc')
                )AS Dt (Data1,Data2)


SET NOCOUNT ON
DECLARE  @Sql nvarchar(max),
         @DynamicColumn nvarchar(max),
         @MaxDynamicColumn nvarchar(max)

SELECT @DynamicColumn = STUFF((SELECT ', '+QUOTENAME(CAST(ReqCol AS VARCHAR(10)))
FROM #TEMP ORDER BY Seq  FOR XML PATH ('')),1,1,'') 

SELECT @MaxDynamicColumn = STUFF((SELECT ', '+'MAX('+QUOTENAME(CAST(ReqCol AS VARCHAR(10)))+') AS '+QUOTENAME(CAST(ReqCol AS VARCHAR(10)))
FROM #TEMP ORDER BY Seq FOR XML PATH ('')),1,1,'') 

SET @Sql='SELECT  Name,'+ @MaxDynamicColumn+'
            FROM
            (
            SELECT * FROM #TEMP
            )AS src
            PIVOT 
            (
            MAX(Data1) FOR [ReqCol] IN ('+@DynamicColumn+')
            ) AS Pvt
            GROUP BY Name '
EXEC (@Sql)
PRINT @Sql
SET NOCOUNT OFF

Result

    Name   CNT1    vol1     Descc1  CNT2    vol2    Descc2
    ---------------------------------------------------------
    Mohan   21       8       Fed     1       391     Fed

let me implement this logic in my code and get back to you @sreenu131 — mohan111, May 16 '19 at 14:19

how to do pivot on multiple columns

2 Answers2