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,582 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 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
  1. MelissaMa-MSFT 24,176 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,176 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,176 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.