SQL Server How to show multiple columns in Pivot

Question

My present query showing period in pivot format but i need to show first period in pivot format and then Period_Comment in pivot format. is it possible

now see how my data looks like. screen shot attached.

i am looking for output like

1Q 2018A 2Q 2018A 3Q 2018A 4Q 2018A FYA 2018 1Q 2019A 2Q 2019A 3Q 2019A 4Q 2019A FYA 2019 1Q 2018A_Comments 2Q 2018A_Comments 3Q 2018A_Comments 4Q 2018A_Comments FYA 2018_Comments 1Q 2019A_Comments 2Q 2019A_Comments 3Q 2019A_Comments 4Q 2019A_Comments FYA 2019_Comments

first showing period in pivot format and after period need to show comments for each period like this way 1Q 2018A_Comments

here i am giving my full store procedure code. please some one guide me how to enhance my code as a result i can show period and period comment in pivot format?

PeriodComments is the column name where comments will be stored for each period. PeriodComments exist in tblTicker_Bogey table

please some one help me to get my desire output. thanks

ALTER PROCEDURE [dbo].[USP_GETBogeyData]     
(    
 @TickerID VARCHAR(10)    
)    
AS    
BEGIN    
    
    SET NOCOUNT ON;    
    DECLARE @columns NVARCHAR(MAX),    
            @columnsAlias NVARCHAR(MAX)    
    DECLARE @sql NVARCHAR(MAX)    
    SET @columns = N''    
    SET @columnsAlias = ''    
    SET @sql = N''    
    
    BEGIN    
    
        DROP TABLE IF EXISTS #tmpPeriod    
        DROP TABLE IF EXISTS #tmpPeriodAll    
		DROP TABLE IF EXISTS #tmpBogey    
    
        ;WITH Hierarchy    
        as (SELECT BogeyID,    
                   ParentID,    
                   SectionID,    
                   LineItemID,    
                   BMID,    
                   PeriodID,    
                   Type,    
				   PeriodValues,    
				   PeriodFormula,    
				   ActualProvidedByCompany,    
                   hierarchy = FORMAT(BogeyID, '0000'),    
                   level = 0,    
                   @TickerID AS TickerID,  
				   SectionComments,  
				   LineItemComments,  
				   BlueMetricsComments,  
				   PeriodComments  				     
            FROM tblTicker_Bogey    
            WHERE ISNULL(ParentID, 0) = 0    
                  AND TickerID = @TickerID    
            UNION ALL    
            SELECT e.BogeyID,    
                   e.ParentID,    
                   e.SectionID,    
                   e.LineItemID,    
                   e.BMID,    
                   e.PeriodID,    
                   e.Type,    
				   e.PeriodValues,    
				   e.PeriodFormula,    
				   e.ActualProvidedByCompany,    
                   hierarchy = d.hierarchy + '.' + FORMAT(e.BogeyID, '0000'),    
                   level = level + 1,    
                   @TickerID AS TickerID,  
				   e.SectionComments,  
				   e.LineItemComments,  
				   e.BlueMetricsComments,  
				   e.PeriodComments  				     
            FROM tblTicker_Bogey e    
                JOIN Hierarchy d    
                    on e.ParentID = d.BogeyID    
            WHERE e.TickerID = @TickerID    
           )    
    
		  SELECT * INTO #tmpBogey FROM    
		  (    
		   SELECT  h.TickerID,    
			 h.BogeyID,    
			 h.ParentID,    
			 h.SectionID,    
			 h.LineItemID,    
			 h.BMID,    
			 s.Section,    
			 l.LineItem,    
			 BM.BM_Element,    
			 h.PeriodID,    
			 h.Type,    
			 h.PeriodValues,    
			 h.PeriodFormula,    
			 h.SectionComments,  
			 h.LineItemComments,  
			 h.BlueMetricsComments,  
			 h.PeriodComments,  
			 h.ActualProvidedByCompany,    
			 s.OrderID AS SectionOrder,    
			 slt.OrderID AS SectionLineItemOrder  
			 FROM Hierarchy h LEFT OUTER JOIN tblSectionLineItemTemplate slt     
			 ON h.SectionID=slt.SectionID AND h.LineItemID=slt.LineItemID AND slt.TickerID=@TickerID    
			 INNER JOIN tblSectionTemplate s ON s.SectionID=slt.SectionID AND s.TickerID=@TickerID AND s.Action<>'D'    
			 INNER JOIN TblLineItemTemplate l ON l.LineItemId=slt.LineItemId AND l.TickerID=@TickerID  AND l.Action<>'D'    
			 LEFT OUTER JOIN tblBlueMetricsMaster BM ON BM.Code=h.BMID    
         
		  ) Y    
  
  
        SELECT DISTINCT    
            A.*,    
            Convert(    
                       numeric,    
                       case    
                           when A.Period like '%1Q%' then    
                               ltrim(rtrim(REPLACE(A.Period, '1Q', ''))) + cast(0 as varchar)    
                           when A.Period like '%2Q%' then    
                               ltrim(rtrim(REPLACE(A.Period, '2Q', ''))) + cast(1 as varchar)    
                           when A.Period like '%1H%' then    
                               ltrim(rtrim(REPLACE(A.Period, '1H', ''))) + cast(2 as varchar)    
                           when A.Period like '%3Q%' then    
                               ltrim(rtrim(REPLACE(A.Period, '3Q', ''))) + cast(3 as varchar)    
                           when    
                           (    
                               A.Period like '%4Q%'    
                               And A.Period not like '%53 Weeks%'    
                           ) then    
                               ltrim(rtrim(REPLACE(REPLACE(A.Period, '4Q', ''), '53 Weeks', ''))) + cast(4 as varchar)    
                           when    
                           (    
                               A.Period like '%4Q%'    
                               And A.Period like '%53 Week%'    
                           ) then    
                               ltrim(rtrim(REPLACE(REPLACE(A.Period, '4Q', ''), '53 Weeks', ''))) + cast(5 as varchar)    
                           when A.Period like '%2H%' then    
								ltrim(rtrim(REPLACE(A.Period, '2H', ''))) + cast(6 as varchar)    
                           when    
                           (    
                               A.Period like '%FY%'    
                               And A.Period not like '%53 Week%'    
                           ) then    
                               ltrim(rtrim((REPLACE(A.Period, 'FY', '')))) + cast(7 as varchar)    
                           when A.Period like '%FY 53 Week%' then    
                               ltrim(rtrim((REPLACE(A.Period, 'FY 53 Weeks', '')))) + cast(8 as varchar)    
                           when A.Period like '%Trans Period%' then    
                               ltrim(rtrim((REPLACE(A.Period, 'Trans Period', '')))) + cast(9 as varchar)    
                       end    
                   ) as PeriodOrder    
        Into #tmpPeriod    
        FROM tblCalenderDetail A    
            INNER JOIN tblCalenderMaster d    
                ON d.ID = A.MasterID    
        WHERE d.TickerID = @TickerId    
              AND A.IsDeleted <> 'Y'    
    
    
        SELECT *    
        INTO #tmpPeriodAll    
        FROM    
        (    
            SELECT TOP 100 PERCENT    
                a.Period,    
                a.Period + IIF(a.PeriodOrder <= cd.PeriodOrder, 'A', 'E') AS NewPeriod,    
                a.PeriodOrder    
            FROM #tmpPeriod a    
                OUTER APPLY    
            (SELECT Period, PeriodOrder FROM #tmpPeriod WHERE IsActual = 'Y') cd    
            ORDER BY a.PeriodOrder    
        ) X    
    
    
        SELECT @columns += N', ' + QUOTENAME([Name], '')    
        FROM    
        (    
            SELECT TOP 100 PERCENT    
                ISNULL(Period, '') AS [Name],    
                PeriodOrder    
            FROM #tmpPeriodAll    
            ORDER BY PeriodOrder    
        ) AS x    
    
    
        SELECT @columnsAlias += N', ' + QUOTENAME([Name], '') + ' AS ' + QUOTENAME(NewPeriod)    
        FROM    
        (    
            SELECT TOP 100 PERCENT    
                ISNULL(Period, '') AS [Name],    
                NewPeriod,    
                PeriodOrder    
            FROM #tmpPeriodAll    
            ORDER BY PeriodOrder    
        ) AS x1    
    
  
		SET @sql= N'Select TickerID,SectionID,LineItemID,BMID,Type,Section,LineItem,BM_Element,SectionOrder,SectionLineItemOrder,    
		AllowedDecimalPlace,CurrencySign,CurrencyCode,AllowPercentageSign,AllowComma,PeriodFormula,SectionComments,LineItemComments,BlueMetricsComments,PeriodComments,    
		' + STUFF(@columnsAlias, 1, 2, '') + '     
		From (                        
		SELECT a.TickerID,a.SectionID,a.LineItemID,a.BMID,a.Type,a.Section,a.LineItem,a.BM_Element,a.SectionOrder,a.SectionLineItemOrder  
		,a.PeriodValues,a.PeriodFormula,a.SectionComments,a.LineItemComments,a.BlueMetricsComments,a.PeriodComments,e.Period [Name],    
		cfg.AllowedDecimalPlace,cfg.CurrencySign,cfg.CurrencyCode,cfg.AllowPercentageSign,cfg.AllowComma    
		FROM #tmpBogey AS a     
		LEFT OUTER JOIN tblCalenderMaster AS d ON a.TickerID = d.TickerID      
		LEFT OUTER JOIN tblCalenderDetail AS e ON d.ID = e.MasterID  AND e.IsDeleted <> ''Y'' AND a.PeriodID=e.ID AND e.MasterID=d.ID    
		LEFT OUTER JOIN tblTicker_LiConfig cfg ON cfg.SectionID=a.SectionID AND cfg.LineitemID=a.LineitemID AND cfg.TickerId=a.TickerId    
		)X PIVOT (MAX(PeriodValues) FOR [Name] IN (' + STUFF(@columns, 1, 1, '')    
					+ ')) AS p                        
		ORDER BY SectionOrder,SectionLineItemOrder'    
    
 --PRINT @sql    
    EXEC (@sql)    
    
    DROP TABLE IF EXISTS #tmpPeriod    
    DROP TABLE IF EXISTS #tmpPeriodAll    
 DROP TABLE IF EXISTS #tmpBogey    
    
    END    
END  

Answer 1

Hi @T.Zacks
If I understand correctly, you want to PIVOT both the PeriodValues column and the PeriodComments column in the #tmpBogey table.
You could use CROSS APPLY first, integrate the PeriodValues column and the PeriodComments column into the same column, and then try PIVOT.
Here is an example which you can refer to:

CREATE TABLE #tmpBogey (Period_ID INT,  
                        Period_Value INT,  
                        Period_Name VARCHAR(30),  
						Period_Comment VARCHAR(30))  
INSERT INTO #tmpBogey VALUES  
(101,111,'1Q 2018A','Comment_1'),(101,222,'2Q 2018A','Comment_2'),  
(101,333,'3Q 2018A','Comment_3'),(101,444,'4Q 2018A','Comment_4'),  
(102,100,'1Q 2018A','Comment_1'),(102,200,'2Q 2018A','Comment_2'),  
(102,300,'3Q 2018A','Comment_3'),(102,400,'4Q 2018A','Comment_4')  
  
DECLARE @columns NVARCHAR(MAX)  
DECLARE @sql NVARCHAR(MAX)   
  
;WITH CTE AS  
(  
 SELECT Period_ID,C.VALUE,C.Column_Names   
 FROM #tmpBogey  
 CROSS APPLY (VALUES(CAST(Period_Value AS VARCHAR(20)),Period_Name),  
                    (Period_Comment,Period_Name+'_Comments'))C(VALUE,Column_Names)  
)  
SELECT @columns= STUFF((SELECT ',[' + Column_Names + ']' FROM CTE   
                        GROUP BY Column_Names   
                        ORDER BY LEN(Column_Names),Column_Names  
                        FOR XML PATH('')),1,1,'')  
--PRINT @columns  
  
SET @sql='  
;WITH CTE AS  
(  
 SELECT Period_ID,C.VALUE,C.Column_Names   
 FROM #tmpBogey  
 CROSS APPLY (VALUES(CAST(Period_Value AS VARCHAR(20)),Period_Name),  
                    (Period_Comment,Period_Name+''_Comments''))C(VALUE,Column_Names)  
)  
SELECT *   
FROM CTE PIVOT(MAX(VALUE)FOR Column_Names IN ('+ @columns +'))P'  
--PRINT @sql  
  
EXEC (@sql)  

Best regards,
LiHong

Answer 2

Take a look at this blog post for the idea of how to solve this problem
https://blogs.lessthandot.com/hugo/ (and the wiki article referenced at the bottom).

Don't use PIVOT operator when you need to pivot multiple columns. Use plain case based aggregations. Try first to produce your desired result using static query with just a few columns. Once you understand it and construct it using static query, convert to dynamic query.