Share via

How to generate nested pivot like output in Sql Server

T.Zacks 3,986 Reputation points
2021-02-10T07:29:29.017+00:00

I do not have very good knowledge in advanced SQL. I have been facing a problem for a long time to show data in a specific way.

Desired output as follows 

+-------------+------------+------+------+------+----------+------+------+------+----------+------+------+------+----------+--+
| DisplayText | Type       | BC   | AK   | NH   | 2010 FYA | BC   | AK   | NH   | 2011 FYA | BC   | AK   | NH   | 2012 FYA |  |
+-------------+------------+------+------+------+----------+------+------+------+----------+------+------+------+----------+--+
| Key Drugs   |   GROUP    | NULL | NULL | NULL |          | NULL | NULL | NULL |          | NULL | NULL | NULL |          |  |
| R&D         |   LINEITEM | 10   | 99   | 12   |          | 56   | 26   | 21   |          | 11   | 87   | 71   |          |  |
| Net Revenue |   LINEITEM | 12   | 20   | 17   |          | 79   | 12   | 22   |          | 55   | 17   | 27   |          |  |
| Key Fin     |   GROUP    | NULL | NULL | NULL |          | NULL | NULL | NULL |          | NULL | NULL | NULL |          |  |
| Diluted     |   LINEITEM | 10   | 99   | 12   |          | 56   | 26   | 21   |          | 11   | 87   | 71   |          |  |
+-------------+------------+------+------+------+----------+------+------+------+----------+------+------+------+----------+--+

I have two tables called tblCSMModels & tblOutputDetl. The tblCSMModels table has parent child data, the relationship is established with ID and ParentID.

Screenshot of the table with sample data:
EHvmt.png

Group are master and line items are child. each group may have multiple child line item and a group may child group too which can have another line items as child.

Here I am showing the table structures and sample data as a result anyone can reproduce this at their end. 

CREATE TABLE [dbo].[tblCSMModels]
(
    [Ticker] [varchar](20) NULL,
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ParentID] [int] NULL,
    [DisplayText] [varchar](max) NULL,
    [Type] [nchar](10) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[tblCSMModels] ON 
GO

INSERT INTO [dbo].[tblCSMModels] ([Ticker], [ID], [ParentID], [DisplayText], [Type]) 
VALUES (N'ANAB', 1, 0, N'Key Drugs', N'GROUP     ')

INSERT INTO [dbo].[tblCSMModels] ([Ticker], [ID], [ParentID], [DisplayText], [Type])  
VALUES (N'ANAB', 2, 1, N'R&D, Proforma', N'LINEITEM  ')

INSERT INTO [dbo].[tblCSMModels] ([Ticker], [ID], [ParentID], [DisplayText], [Type]) 
VALUES (N'ANAB', 3, 1, N'Net Revenue', N'LINEITEM  ')

INSERT INTO [dbo].[tblCSMModels] ([Ticker], [ID], [ParentID], [DisplayText], [Type]) 
VALUES (N'ANAB', 4, 0, N'Key Fin', N'GROUP     ')

INSERT INTO [dbo].[tblCSMModels] ([Ticker], [ID], [ParentID], [DisplayText], [Type]) 
VALUES (N'ANAB', 5, 4, N'Diluted', N'LINEITEM  ')
GO

CREATE TABLE [dbo].[tblOutputDetl]
(
    [Ticker] [varchar](20) NULL,
    [Section] [varchar](max) NULL,
    [LineItem] [varchar](max) NULL,
    [Broker] [varchar](max) NULL,
    [Period] [varchar](20) NULL,
    [ItemValue] [decimal](18, 2) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO tblOutputDetl (Ticker, Section, LineItem, Broker, Period, ItemValue)
VALUES ('ANAB','Consensus Model','R&D','BC','2010 FYA',10),
       ('ANAB','Consensus Model','R&D','BC','2011 FYA',56),
       ('ANAB','Consensus Model','R&D','BC','2012 FYA',11),
       ('ANAB','Consensus Model','R&D','AK','2010 FYA',99),
       ('ANAB','Consensus Model','R&D','AK','2011 FYA',26),
       ('ANAB','Consensus Model','R&D','AK','2012 FYA',87),
       ('ANAB','Consensus Model','R&D','NH','2010 FYA',12),
       ('ANAB','Consensus Model','R&D','NH','2011 FYA',21),
       ('ANAB','Consensus Model','R&D','NH','2012 FYA',71),
       ('ANAB','Consensus Model','Net Revenue','NH','2010 FYA',12),
       ('ANAB','Consensus Model','Net Revenue','NH','2011 FYA',79),
       ('ANAB','Consensus Model','Net Revenue','NH','2012 FYA',55),
       ('ANAB','Consensus Model','Net Revenue','NH','2010 FYA',20),
       ('ANAB','Consensus Model','Net Revenue','NH','2011 FYA',12),
       ('ANAB','Consensus Model','Net Revenue','NH','2012 FYA',17),
       ('ANAB','Consensus Model','Net Revenue','NH','2010 FYA',21),
       ('ANAB','Consensus Model','Net Revenue','NH','2011 FYA',22),
       ('ANAB','Consensus Model','Net Revenue','NH','2012 FYA',27),
       ('ANAB','Key Drugs','Diluted','BC','2010 FYA',10),
       ('ANAB','Key Drugs','Diluted','BC','2011 FYA',56),
       ('ANAB','Key Drugs','Diluted','BC','2012 FYA',11),
       ('ANAB','Key Drugs','Diluted','AK','2010 FYA',99),
       ('ANAB','Key Drugs','Diluted','AK','2011 FYA',26),
       ('ANAB','Key Drugs','Diluted','AK','2012 FYA',87),
       ('ANAB','Key Drugs','Diluted','NH','2010 FYA',12),
       ('ANAB','Key Drugs','Diluted','NH','2011 FYA',21),
       ('ANAB','Key Drugs','Diluted','NH','2012 FYA',71)

This way tried but could not write the correct SQL which produces the desired output. 

;WITH DirectReports (Ticker, ID, ParentID, DisplayText, Type) AS
(
    SELECT Ticker, ID, ParentID, DisplayText, Type
    FROM tblCSMModels AS e
    WHERE isnull(ParentID ,0) = 0

    UNION ALL

    SELECT e.Ticker, e.ID, e.ParentID, e.DisplayText, e.Type
    FROM tblCSMModels AS e
    INNER JOIN DirectReports AS d ON e.ParentID = d.ID
)

SELECT * 
FROM
    (SELECT r.Ticker, r.DisplayText, r.Type, d.Broker, d.ItemValue, d.Period
     FROM DirectReports r 
     INNER JOIN tblOutputDetl d ON (r.Ticker = d.Ticker AND r.DisplayText = d.LineItem)
     WHERE r.Ticker = 'ANAB') t
PIVOT
    (MAX(ItemValue)
        FOR Broker IN ([BC], [AK], [NH])
    ) AS P

Basically I want to show parent data and their related child. Broker will be coming horizontally and ItemValue will be placed under broker. after showing all broker horizontally then one Period will come say 2010 FYA.

Period will be sorted like 2010 FYA, 2011 FYA, 2012 FYA, 1Q 2013A,2Q 2013A,3Q 2013A,4Q 2013A, 2013 FYA. Period is not fixed. it will vary ticker wise. some ticker period start from 2010 FY to 2080 FY

Broker is also not fixed. Here i mention 3 brokers but it can be 40 or more in real scenario.

This way specific period wise Broker data will be shown horizontally. so all brokers and period will be showing horizontally. see my output first at top with in ASCII table.

Under each period of every row SUM of Broker value should be shown.

I can understand to get desired output SQL will be very complex and I am not being able to write that SQL. So my request please some one help me. I badly stuck at this SQL from morning.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
0 comments
Accepted answer
  1. MelissaMa-MSFT 24,191 Reputation points
    2021-02-12T07:44:40.68+00:00

    Hi @T.Zacks ,

    Please refer below updated one and check whether it is helpful to you. Thanks. 

    ;WITH DirectReports (Ticker, ID, ParentID, DisplayText, Type) AS  
  (  
      SELECT Ticker, ID, ParentID, DisplayText, Type  
      FROM tblCSMModels AS e  
      WHERE isnull(ParentID ,0) = 0  
          
      UNION ALL  
          
      SELECT e.Ticker, e.ID, e.ParentID, e.DisplayText, e.Type  
      FROM tblCSMModels AS e  
      INNER JOIN DirectReports AS d ON e.ParentID = d.ID  
  )  
 ,cte as (  
 SELECT r.Ticker, r.DisplayText, r.Type, d.Broker, d.ItemValue, d.Period  
       FROM DirectReports r   
       left JOIN tblOutputDetl d ON (r.Ticker = d.Ticker AND   
   d.LineItem = case when CHARINDEX(',',r.DisplayText)>0 then SUBSTRING(r.DisplayText,1,CHARINDEX(',',r.DisplayText)-1) else r.DisplayText end)  
       WHERE r.Ticker = 'ANAB')  
 ,cte1 as (  
  SELECT *   
  FROM  
      (SELECT Ticker, DisplayText, Type, Broker, ItemValue, Period  
       from cte) t  
  PIVOT  
      (MAX(ItemValue)  
          FOR Broker IN ([BC], [AK], [NH])  
      ) AS P)  
 ,cte2 as (  
 SELECT *, Period+'_BC' as Col, BC as Val FROM cte1   
  UNION  
  SELECT *, Period+'_AK' as Col,AK as Val  FROM cte1  
   UNION  
  SELECT *, Period+'_NH' as Col,NH as Val  FROM cte1  
  UNION  
  SELECT *, Period as Col,NULL as Val  FROM cte1  
  )  
  select DisplayText,TYPE,[2010 FYA_BC] [BC],[2010 FYA_AK] [AK], [2010 FYA_NH] [NH],[2010 FYA],[2011 FYA_BC] [BC],[2011 FYA_AK] [AK],  
  [2011 FYA_NH] [NH],[2011 FYA],[2012 FYA_BC] [BC],[2012 FYA_AK] [AK],[2012 FYA_NH] [NH],[2012 FYA]  
   from   
  (select DisplayText,type,col,val  
  from cte2) s  
  PIVOT (   
  max(val)   
  for Col in   
  ([2010 FYA_BC],[2010 FYA_AK], [2010 FYA_NH],[2010 FYA],[2011 FYA_BC],[2011 FYA_AK],  
  [2011 FYA_NH],[2011 FYA],[2012 FYA_BC],[2012 FYA_AK],[2012 FYA_NH],[2012 FYA]) ) AS pvt

    Output:
    67393-output.png

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. MelissaMa-MSFT 24,191 Reputation points
    2021-02-10T08:53:11.863+00:00

    Hi @T.Zacks ,

    Welcome to Microsoft Q&A!

    Actually it is much difficulty to write the query according to your expected output.

    Besides, I made some modifications on your insert statement to achieve the similar output.

    66343-sample.png

    I took a long time and could get below output instead. Please help check whether it is helpful to you. 

    ;WITH DirectReports (Ticker, ID, ParentID, DisplayText, Type) AS  
 (  
     SELECT Ticker, ID, ParentID, DisplayText, Type  
     FROM tblCSMModels AS e  
     WHERE isnull(ParentID ,0) = 0  
      
     UNION ALL  
      
     SELECT e.Ticker, e.ID, e.ParentID, e.DisplayText, e.Type  
     FROM tblCSMModels AS e  
     INNER JOIN DirectReports AS d ON e.ParentID = d.ID  
 )  
,cte as (  
SELECT r.Ticker, r.DisplayText, r.Type, d.Broker, d.ItemValue, d.Period  
      FROM DirectReports r   
      left JOIN tblOutputDetl d ON (r.Ticker = d.Ticker AND   
  d.LineItem = case when CHARINDEX(',',r.DisplayText)>0 then SUBSTRING(r.DisplayText,1,CHARINDEX(',',r.DisplayText)-1) else r.DisplayText end)  
      WHERE r.Ticker = 'ANAB')  
,cte1 as (  
 SELECT *   
 FROM  
     (SELECT Ticker, DisplayText, Type, Broker, ItemValue, Period  
      from cte) t  
 PIVOT  
     (MAX(ItemValue)  
         FOR Broker IN ([BC], [AK], [NH])  
     ) AS P)  
,cte2 as (  
SELECT *, Period+'_BC' as Col, BC as Val FROM cte1   
 UNION  
 SELECT *, Period+'_AK' as Col,AK as Val  FROM cte1  
  UNION  
 SELECT *, Period+'_NH' as Col,NH as Val  FROM cte1)  
 select * from   
 (select DisplayText,type,col,val  
 from cte2) s  
 PIVOT (   
 max(val)   
 for Col in   
 ([2010 FYA_BC],[2010 FYA_AK], [2010 FYA_NH],[2011 FYA_BC],[2011 FYA_AK],  
 [2011 FYA_NH],[2012 FYA_BC],[2012 FYA_AK],[2012 FYA_NH]) ) AS pvt

    Output:

    66306-output.png

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
  2. MelissaMa-MSFT 24,191 Reputation points
    2021-02-17T07:33:39.307+00:00

    Hi @T.Zacks ,

    I made another dynamic statement and used global temple table instead of temple table.

    Please refer below updated one: 

    drop table if exists  ##temp,##temp1,##temp2  
   
 declare @sql1 nvarchar(max)=''  
 declare @s2 nvarchar(max)=''  
 declare @s3 nvarchar(max)=''  
   
       
 ;WITH DirectReports (Ticker, ID, ParentID, DisplayText, Type) AS  
 (  
     SELECT Ticker, ID, ParentID, DisplayText, Type  
     FROM tblCSMModels AS e  
     WHERE isnull(ParentID ,0) = 0  
                  
     UNION ALL  
                  
     SELECT e.Ticker, e.ID, e.ParentID, e.DisplayText, e.Type  
     FROM tblCSMModels AS e  
     INNER JOIN DirectReports AS d ON e.ParentID = d.ID  
 )  
  
 SELECT r.Ticker, r.DisplayText, r.Type, d.Broker, d.ItemValue, d.Period  
 into ##temp2  
     FROM DirectReports r   
     left JOIN tblOutputDetl d ON (r.Ticker = d.Ticker AND   
 d.LineItem = case when CHARINDEX(',',r.DisplayText)>0 then SUBSTRING(r.DisplayText,1,CHARINDEX(',',r.DisplayText)-1) else r.DisplayText end)  
     WHERE r.Ticker = 'ANAB'  
  
 SELECT @s2 = STUFF((  
     SELECT distinct ',' + '['+Broker+']'  
     FROM ##temp2  
     FOR XML PATH('')  
     ), 1, 1, '')  
 FROM ##temp2  
  
SELECT @s3 = STUFF((  
     SELECT distinct ' SELECT *, Period+''_'+Broker+''' as Col, '+Broker+' as Val FROM cte1 UNION '  
     FROM ##temp2  
     FOR XML PATH('')  
     ), 1, 1, '')  
 FROM ##temp2  
  
set @sql1=N';with cte1 as (  
 SELECT *   
 FROM  
     (SELECT Ticker, DisplayText, Type, Broker, ItemValue, Period  
     from ##temp2) t  
 PIVOT  
     (MAX(ItemValue)  
         FOR Broker IN ('+@s2+')  
     ) AS P)  
 ,cte2 as (  
 '+@s3+'  
 SELECT *, Period as Col,NULL as Val  FROM cte1  
 )  
 select * into ##temp   
 from cte2'  
  
 EXECUTE sp_executesql  @sql1  
      
 select distinct col  
 into ##temp1  
 from ##temp   
 where col is not null    
      
 declare @sql nvarchar(max)=''  
 declare @s nvarchar(max)=''  
 declare @s1 nvarchar(max)=''  
          
 SELECT @s = STUFF((  
     SELECT ',' + '['+col+']'  
     FROM ##temp1  
     order by col desc  
     FOR XML PATH('')  
     ), 1, 1, '')  
 FROM ##temp1  
      
 SELECT @s1 = STUFF((  
     SELECT ',' + '['+col+']'+ case when CHARINDEX('_',col)>0 then '['+right(col,2)+']' else '' end  
     FROM ##temp1  
     order by col desc  
     FOR XML PATH('')  
     ), 1, 1, '')  
 FROM ##temp1  
      
 select @sql=N'  
      
    select DisplayText,TYPE,'+@s1+'  
     from   
    (select DisplayText,type,col,val  
    from ##temp) s  
    PIVOT (   
    max(val)   
    for Col in   
    ('+@s+') ) AS pvt'  
      
 EXECUTE sp_executesql  @sql

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
  3. Stefan Hoffmann 621 Reputation points
    2021-02-10T09:11:19.15+00:00

    The question is: Is the pivot structure invariant or not?

    In the first case it is a more or less simple , classic pivot. In the second case you need dynamic SQL. And having a Cartesian product of Broker and Period may lead to a column explosion. So in this case, when it's a reporting scenario - and it looks like one - do the pivoting in the report generator.

    Simple, classic: 

     SELECT   TOD.Ticker ,
             TOD.Section ,
             TOD.LineItem ,
             SUM(IIF(TOD.Period = '2010 FYA' AND TOD.Broker = 'BC', TOD.ItemValue, 0)) AS BC_2010_FYA ,
             SUM(IIF(TOD.Period = '2010 FYA' AND TOD.Broker = 'AK', TOD.ItemValue, 0)) AS AK_2010_FYA ,
             SUM(IIF(TOD.Period = '2010 FYA' AND TOD.Broker = 'NH', TOD.ItemValue, 0)) AS NH_2010_FYA ,
             SUM(IIF(TOD.Period = '2011 FYA' AND TOD.Broker = 'BC', TOD.ItemValue, 0)) AS BC_2012_FYA ,
             SUM(IIF(TOD.Period = '2011 FYA' AND TOD.Broker = 'AK', TOD.ItemValue, 0)) AS AK_2012_FYA ,
             SUM(IIF(TOD.Period = '2011 FYA' AND TOD.Broker = 'NH', TOD.ItemValue, 0)) AS NH_2012_FYA ,
             SUM(IIF(TOD.Period = '2012 FYA' AND TOD.Broker = 'BC', TOD.ItemValue, 0)) AS BC_2012_FYA ,
             SUM(IIF(TOD.Period = '2012 FYA' AND TOD.Broker = 'AK', TOD.ItemValue, 0)) AS AK_2012_FYA ,
             SUM(IIF(TOD.Period = '2012 FYA' AND TOD.Broker = 'NH', TOD.ItemValue, 0)) AS NH_2012_FYA
    FROM     #tblOutputDetl TOD
    GROUP BY TOD.Ticker ,
             TOD.Section ,
             TOD.LineItem;

    Just join the result with your line item table.

  4. MelissaMa-MSFT 24,191 Reputation points
    2021-02-16T09:34:23.613+00:00

    Hi @T.Zacks ,

    Please refer below dynamic method: 

    drop table if exists  #temp,#temp1  
   
;WITH DirectReports (Ticker, ID, ParentID, DisplayText, Type) AS  
(  
    SELECT Ticker, ID, ParentID, DisplayText, Type  
    FROM tblCSMModels AS e  
    WHERE isnull(ParentID ,0) = 0  
              
    UNION ALL  
              
    SELECT e.Ticker, e.ID, e.ParentID, e.DisplayText, e.Type  
    FROM tblCSMModels AS e  
    INNER JOIN DirectReports AS d ON e.ParentID = d.ID  
)  
,cte as (  
SELECT r.Ticker, r.DisplayText, r.Type, d.Broker, d.ItemValue, d.Period  
    FROM DirectReports r   
    left JOIN tblOutputDetl d ON (r.Ticker = d.Ticker AND   
d.LineItem = case when CHARINDEX(',',r.DisplayText)>0 then SUBSTRING(r.DisplayText,1,CHARINDEX(',',r.DisplayText)-1) else r.DisplayText end)  
    WHERE r.Ticker = 'ANAB')  
,cte1 as (  
SELECT *   
FROM  
    (SELECT Ticker, DisplayText, Type, Broker, ItemValue, Period  
    from cte) t  
PIVOT  
    (MAX(ItemValue)  
        FOR Broker IN ([BC], [AK], [NH])  
    ) AS P)  
,cte2 as (  
SELECT *, Period+'_BC' as Col, BC as Val FROM cte1   
UNION  
SELECT *, Period+'_AK' as Col,AK as Val  FROM cte1  
UNION  
SELECT *, Period+'_NH' as Col,NH as Val  FROM cte1  
UNION  
SELECT *, Period as Col,NULL as Val  FROM cte1  
)  
select * into #temp   
from cte2  
  
select distinct col  
into #temp1  
from #temp   
where col is not null    
  
declare @sql nvarchar(max)=''  
declare @s nvarchar(max)=''  
declare @s1 nvarchar(max)=''  
      
SELECT @s = STUFF((  
    SELECT ',' + '['+col+']'  
    FROM #temp1  
	order by col desc  
    FOR XML PATH('')  
    ), 1, 1, '')  
FROM #temp1  
  
SELECT @s1 = STUFF((  
    SELECT ',' + '['+col+']'+ case when CHARINDEX('_',col)>0 then '['+right(col,2)+']' else '' end  
    FROM #temp1  
	order by col desc  
    FOR XML PATH('')  
    ), 1, 1, '')  
FROM #temp1  
  
select @sql=N'  
  
   select DisplayText,TYPE,'+@s1+'  
    from   
   (select DisplayText,type,col,val  
   from #temp) s  
   PIVOT (   
   max(val)   
   for Col in   
   ('+@s+') ) AS pvt'  
  
EXECUTE sp_executesql  @sql

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.