How to add Extra Header as rows on result of pivot ?

ahmed salah 3,216 Reputation points
2021-11-17T07:29:27.637+00:00

I work on sql server 2012 i need to add extra row as same header
to result of pivot

my code as below :

IF OBJECT_ID('tempdb..#TempData')IS NOT NULL
DROP TABLE #TempData
;WITH CTE(SrNo , InvoiceDate , InvoiceNo , PayerName , IGMNo , ContainerNo , Size , [Type] , CHACode , CHAName , ActGateinDate , ContainerAgent , ContainerAgentName , ImporterName , ActivityDescription , Amount )
AS
(
SELECT 1 , '7-1-2018 12:42:56 AM' , 'MII180700001' , 'PROMEDIA TRADING PRIVATE LIMITED' , 2198105 , 'DFSU1696812' , 20 , 'GB' ,NULL ,NULL , '6-28-2018 9:32:02 PM' , 'AACCT8966D' , 'T S LINES AGENCY' , 'PROMEDIATRADINGPRIVAT' , 'Contrainer Ground Rent Charges' , 650 UNION ALL
SELECT 2 , '7-1-2018 12:42:56 AM' , 'MII180700001' , 'PROMEDIA TRADING PRIVATE LIMITED' , 2198105 , 'DFSU1696812' , 20 , 'GB' ,NULL ,NULL , '6-28-2018 9:32:02 PM' , 'AACCT8966D' , 'T S LINES AGENCY' , 'PROMEDIATRADINGPRIVAT' , 'Fuel Charges' , 850 UNION ALL
SELECT 3 , '7-1-2018 12:42:56 AM' , 'MII180700001' , 'PROMEDIA TRADING PRIVATE LIMITED' , 2198105 , 'DFSU1696812' , 20 , 'GB' ,NULL ,NULL , '6-28-2018 9:32:02 PM' , 'AACCT8966D' , 'T S LINES AGENCY' , 'PROMEDIATRADINGPRIVAT' , 'Handling and PNR Movement Charges' ,7400 UNION ALL
SELECT 4 , '7-1-2018 12:42:56 AM' , 'MII180700001' , 'PROMEDIA TRADING PRIVATE LIMITED' , 2198105 , 'DFSU1696812' , 20 , 'GB' ,NULL ,NULL , '6-28-2018 9:32:02 PM' , 'AACCT8966D' , 'T S LINES AGENCY' , 'PROMEDIATRADINGPRIVAT' , 'Weighment Charges' ,200 UNION ALL
SELECT 5 , '7-1-2018 12:42:56 AM' , 'MII180700001' , 'PROMEDIA TRADING PRIVATE LIMITED' , 2198105 , 'DFSU1699560' , 20 , 'GB' ,NULL ,NULL , '6-29-2018 12:10:52 AM' , 'AACCT8966D' , 'T S LINES AGENCY' , 'PROMEDIATRADINGPRIVAT' , 'Container Movement and Increase Charges' ,1800 UNION ALL
SELECT 6 , '7-1-2018 12:42:56 AM' , 'MII180700001' , 'PROMEDIA TRADING PRIVATE LIMITED' , 2198105 , 'DFSU1699560' , 20 , 'GB' ,NULL ,NULL , '6-29-2018 12:10:52 AM' , 'AACCT8966D' , 'T S LINES AGENCY' , 'PROMEDIATRADINGPRIVAT' , 'Documentation Charges' ,250 UNION ALL
SELECT 7 , '7-1-2018 12:42:56 AM' , 'MII180700001' , 'PROMEDIA TRADING PRIVATE LIMITED' , 2198105 , 'DFSU1699560' , 20 , 'GB' ,NULL ,NULL , '6-29-2018 12:10:52 AM' , 'AACCT8966D' , 'T S LINES AGENCY' , 'PROMEDIATRADINGPRIVAT' , 'Fuel Charges' ,850 UNION ALL
SELECT 8 , '7-1-2018 12:42:56 AM' , 'MII180700001' , 'PROMEDIA TRADING PRIVATE LIMITED' , 2198105 , 'DFSU1699560' , 20 , 'GB' ,NULL ,NULL , '6-29-2018 12:10:52 AM' , 'AACCT8966D' , 'T S LINES AGENCY' , 'PROMEDIATRADINGPRIVAT' , 'Handling and PNR Movement Charges' ,7400 UNION ALL
SELECT 9 , '7-1-2018 12:42:56 AM' , 'MII180700001' , 'PROMEDIA TRADING PRIVATE LIMITED' , 2198105 , 'DFSU1699560' , 20 , 'GB' ,NULL ,NULL , '6-29-2018 12:10:52 AM' , 'AACCT8966D' , 'T S LINES AGENCY' , 'PROMEDIATRADINGPRIVAT' , 'Insurance Charges' ,300 UNION ALL
SELECT 10 , '7-1-2018 12:42:56 AM' , 'MII180700001' , 'PROMEDIA TRADING PRIVATE LIMITED' , 2198105 , 'DFSU1699560' , 20 , 'GB' ,NULL ,NULL , '6-29-2018 12:10:52 AM' , 'AACCT8966D' , 'T S LINES AGENCY' , 'PROMEDIATRADINGPRIVAT' , 'Weighment Charges' ,200 UNION ALL
SELECT 11 , '7-1-2018 12:42:56 AM' , 'MII180700001' , 'PROMEDIA TRADING PRIVATE LIMITED' , 2198105 , 'DFSU1696812' , 20 , 'GB' ,NULL ,NULL , '6-28-2018 9:32:02 PM' , 'AACCT8966D' , 'T S LINES AGENCY' , 'PROMEDIATRADINGPRIVAT' , 'Container Movement and Increase Charges',1800 UNION ALL
SELECT 12 , '7-1-2018 12:42:56 AM' , 'MII180700001' , 'PROMEDIA TRADING PRIVATE LIMITED' , 2198105 , 'DFSU1696812' , 20 , 'GB' ,NULL ,NULL , '6-28-2018 9:32:02 PM' , 'AACCT8966D' , 'T S LINES AGENCY' , 'PROMEDIATRADINGPRIVAT' , 'Container Tracking Charges' ,100 UNION ALL
SELECT 13 , '7-1-2018 12:42:56 AM' , 'MII180700001' , 'PROMEDIA TRADING PRIVATE LIMITED' , 2198105 , 'DFSU1696812' , 20 , 'GB' ,NULL ,NULL , '6-28-2018 9:32:02 PM' , 'AACCT8966D' , 'T S LINES AGENCY' , 'PROMEDIATRADINGPRIVAT' , 'Documentation Charges' ,250 UNION ALL
SELECT 14 , '7-1-2018 12:42:56 AM' , 'MII180700001' , 'PROMEDIA TRADING PRIVATE LIMITED' , 2198105 , 'DFSU1696812' , 20 , 'GB' ,NULL ,NULL , '6-28-2018 9:32:02 PM' , 'AACCT8966D' , 'T S LINES AGENCY' , 'PROMEDIATRADINGPRIVAT' , 'Seal Charges' ,0 UNION ALL
SELECT 15 , '7-1-2018 12:42:56 AM' , 'MII180700001' , 'PROMEDIA TRADING PRIVATE LIMITED' , 2198105 , 'DFSU1699560' , 20 , 'GB' ,NULL ,NULL , '6-29-2018 12:10:52 AM' , 'AACCT8966D' , 'T S LINES AGENCY' , 'PROMEDIATRADINGPRIVAT' , 'Container Tracking Charges' ,100 UNION ALL
SELECT 16 , '7-1-2018 12:42:56 AM' , 'MII180700001' , 'PROMEDIA TRADING PRIVATE LIMITED' , 2198105 , 'DFSU1699560' , 20 , 'GB' ,NULL ,NULL , '6-29-2018 12:10:52 AM' , 'AACCT8966D' , 'T S LINES AGENCY' , 'PROMEDIATRADINGPRIVAT' , 'Seal Charges' ,0 UNION ALL
SELECT 17 , '7-1-2018 12:42:56 AM' , 'MII180700001' , 'PROMEDIA TRADING PRIVATE LIMITED' , 2198105 , 'DFSU1696812' , 20 , 'GB' ,NULL ,NULL , '6-28-2018 9:32:02 PM' , 'AACCT8966D' , 'T S LINES AGENCY' , 'PROMEDIATRADINGPRIVAT' , 'Insurance Charges' ,300
)
SELECT * INTO #TempData FROM CTE

   DECLARE @Columns VARCHAR(MAX) ='',
            @Columns2 VARCHAR(MAX) ='',
            @Sql nvarchar (max)=''

    SELECT @Columns=STUFF((SELECT DISTINCT ',',+ QUOTENAME([ActivityDescription] )
                    FROM  #TempData FOR XML PATH ('')),1,1,'')

    SELECT @Columns
    SELECT @Columns2=STUFF((SELECT DISTINCT ',',+ 'MAX('+QUOTENAME([ActivityDescription] ) +') AS '+QUOTENAME([ActivityDescription] )
                    FROM  #TempData FOR XML PATH ('')),1,1,'')

    SET @sql = 'SELECT  [PayerName],
                        [ContainerNo],
                        [Size],
                        [Type],
                        [CHAName],
                        [ActGateinDate], 
                        [ContainerAgentName],
                        [ImporterName],'+ @Columns2+
                'FROM
                (SELECT * FROM  #TempData) AS Src
                PIVOT(MAX([Amount]) FOR [ActivityDescription] IN ('+ @Columns +')
                )pv  
                GROUP BY [PayerName],[ContainerNo],[Size],[Type],
                        [CHAName],[ActGateinDate], [ContainerAgentName],
                        [ImporterName]'

    PRINT(@sql)
    EXEC (@sql)

I need to add dynamic header based on pivot columns added
[PayerName],[ContainerNo],[Size], [Type],[CHAName],[ActGateinDate],[ContainerAgentName], [ImporterName],[Container Movement and Increase] Charges],[Container Tracking Charges],[Contrainer Ground Rent Charges],[Documentation Charges],[Fuel Charges],[Handling and PNR Movement Charges],[Insurance Charges],[Seal Charges], [Weighment Charges]

so result must be
select [PayerName],[ContainerNo],[Size], [Type],[CHAName],[ActGateinDate],[ContainerAgentName], [ImporterName],[Container Movement and Increase] Charges],[Container Tracking Charges],[Contrainer Ground Rent Charges],[Documentation Charges],[Fuel Charges],[Handling and PNR Movement Charges],[Insurance Charges],[Seal Charges], [Weighment Charges]
union all
pivot
so How to do that please

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,754 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,554 questions
0 comments
Accepted answer
  1. Guoxiong 8,126 Reputation points
    2021-11-18T03:42:29.777+00:00

    Try this:

    DECLARE @Columns VARCHAR(MAX) = '',
        @Columns2 VARCHAR(MAX) = '',
        @Sql NVARCHAR(MAX) = '';

SELECT @Columns = STUFF((SELECT DISTINCT ',', + QUOTENAME([ActivityDescription])
  FROM #TempData FOR XML PATH ('')), 1, 1, '');

SELECT @Columns2 = STUFF((SELECT DISTINCT ',', + 'MAX('+QUOTENAME([ActivityDescription]) + ') AS ' + QUOTENAME([ActivityDescription])
  FROM #TempData FOR XML PATH ('')), 1, 1, '');

SET @Columns2 = REPLACE(@Columns2, 'MAX([Container Movement and Increase Charges])', 'CAST(MAX([Container Movement and Increase Charges]) AS VARCHAR(20))');
SET @Columns2 = REPLACE(@Columns2, 'MAX([Container Tracking Charges])', 'CAST(MAX([Container Tracking Charges]) AS VARCHAR(20))');
SET @Columns2 = REPLACE(@Columns2, 'MAX([Contrainer Ground Rent Charges])', 'CAST(MAX([Contrainer Ground Rent Charges]) AS VARCHAR(20))');
SET @Columns2 = REPLACE(@Columns2, 'MAX([Documentation Charges])', 'CAST(MAX([Documentation Charges]) AS VARCHAR(20))');
SET @Columns2 = REPLACE(@Columns2, 'MAX([Fuel Charges])', 'CAST(MAX([Fuel Charges]) AS VARCHAR(20))');
SET @Columns2 = REPLACE(@Columns2, 'MAX([Handling and PNR Movement Charges])', 'CAST(MAX([Handling and PNR Movement Charges]) AS VARCHAR(20))');
SET @Columns2 = REPLACE(@Columns2, 'MAX([Insurance Charges])', 'CAST(MAX([Insurance Charges]) AS VARCHAR(20))');
SET @Columns2 = REPLACE(@Columns2, 'MAX([Seal Charges])', 'CAST(MAX([Seal Charges]) AS VARCHAR(20))');
SET @Columns2 = REPLACE(@Columns2, 'MAX([Weighment Charges])', 'CAST(MAX([Weighment Charges]) AS VARCHAR(20))');

--PRINT(@Columns2)

--DROP TABLE IF EXISTS ##Temp_Output; 
IF OBJECT_ID(N'tempdb..##Temp_Output') IS NOT NULL
BEGIN
    DROP TABLE ##Temp_Output;
END

SET @sql = '
SELECT  
    [PayerName],
    [ContainerNo],
    CAST([Size] AS VARCHAR(20)) AS [Size],
    [Type],
    CAST([CHAName] AS VARCHAR(20)) AS [CHAName],
    [ActGateinDate], 
    [ContainerAgentName],
    [ImporterName],' + 
    @Columns2 + '
INTO ##Temp_Output 
FROM (
    SELECT * 
    FROM  #TempData) AS Src
    PIVOT(MAX([Amount]) FOR [ActivityDescription] IN (' + @Columns +  ')
) pv  
GROUP BY [PayerName], [ContainerNo], [Size], [Type], [CHAName], [ActGateinDate], [ContainerAgentName], [ImporterName]
';

PRINT(@sql)
EXEC(@sql)

--DROP TABLE IF EXISTS ##Temp_Columns;
IF OBJECT_ID(N'tempdb..##Temp_Columns') IS NOT NULL
BEGIN
    DROP TABLE ##Temp_Columns;
END

SELECT [column_id], [name] 
INTO ##Temp_Columns
FROM tempdb.sys.columns 
WHERE object_id = OBJECT_ID('tempdb..##Temp_Output') 

SELECT @Columns = STUFF(
    (
        SELECT ',',+ QUOTENAME([name])
        FROM ##Temp_Columns 
        ORDER BY [column_id]
        FOR XML PATH ('')
    ),
    1,
    1,
    ''
);
--PRINT(@Columns)

--DROP TABLE IF EXISTS ##Temp_Columns_Pivot;
IF OBJECT_ID(N'tempdb..##Temp_Columns_Pivot') IS NOT NULL
BEGIN
    DROP TABLE ##Temp_Columns_Pivot;
END

SET @sql = N'
    SELECT * 
    INTO ##Temp_Columns_Pivot
    FROM (
        SELECT [name]
        FROM ##Temp_Columns 
    ) AS src
    PIVOT (
        MAX([name]) FOR [name] IN (' + @Columns +  ')
    ) AS pvt
'
EXEC(@sql);

-- Final output
SELECT * FROM ##Temp_Columns_Pivot
UNION ALL
SELECT * FROM ##Temp_Output;

--Drop temporary tables
IF OBJECT_ID(N'tempdb..##Temp_Columns') IS NOT NULL
BEGIN
    DROP TABLE ##Temp_Columns;
END
IF OBJECT_ID(N'tempdb..##Temp_Columns_Pivot') IS NOT NULL
BEGIN
    DROP TABLE ##Temp_Columns_Pivot;
END
IF OBJECT_ID(N'tempdb..##Temp_Output') IS NOT NULL
BEGIN
    DROP TABLE ##Temp_Output;
END

IF OBJECT_ID(N'tempdb..#TempData') IS NOT NULL
BEGIN
    DROP TABLE #TempData;
END
GO

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-11-18T06:14:05.147+00:00

    Hi @ahmed salah ,

    You can insert column names into a temporary table, then insert the output of the dynamic process into another temporary table, and then use UNION ALL to output the two temporary tables together: 

    DROP TABLE IF EXISTS #t  
  
    DECLARE @Columns VARCHAR(MAX) ='',  
             @Columns2 VARCHAR(MAX) ='',  
             @Sql nvarchar (max)=''  
      
     SELECT @Columns=STUFF((SELECT DISTINCT ',',+ QUOTENAME([ActivityDescription] )  
                     FROM  #TempData FOR XML PATH ('')),1,1,'')  
      
       
     SELECT @Columns2=STUFF((SELECT DISTINCT ',',+ 'MAX('+QUOTENAME([ActivityDescription] ) +') AS '+QUOTENAME([ActivityDescription] )  
                     FROM  #TempData FOR XML PATH ('')),1,1,'')  
     SELECT @Columns  val  
  INTO #t  
  
     SET @sql = 'SELECT  [PayerName],  
                         [ContainerNo],  
                         [Size],  
                         [Type],  
                         [CHAName],  
                         [ActGateinDate],   
                         [ContainerAgentName],  
                         [ImporterName],'+ @Columns2+  
                 'FROM  
                 (SELECT * FROM  #TempData) AS Src  
                 PIVOT(MAX([Amount]) FOR [ActivityDescription] IN ('+ @Columns +')  
                 )pv    
                 GROUP BY [PayerName],[ContainerNo],[Size],[Type],  
                         [CHAName],[ActGateinDate], [ContainerAgentName],  
                         [ImporterName]'  
      
     PRINT(@sql)  
     EXEC (@sql)  
  
  
;WITH cte  
as(SELECT REPLACE(REPLACE([value],'[',''),']','') [value]  
,ROW_NUMBER() OVER(ORDER BY [value]) rr FROM #t  
CROSS APPLY STRING_SPLIT(val,',') v)  
  
SELECT '[PayerName]','[ContainerNo]','[Size]',  
'[Type]','[CHAName]','[ActGateinDate]',   
'[ContainerAgentName]','[ImporterName]',  
[1],[2],[3],[4],[5],[6],[7],[8],[9] FROM cte   
PIVOT (MAX([value]) FOR rr IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])) p

    150444-image.png 

    ;WITH cte  
as(SELECT REPLACE(REPLACE([value],'[',''),']','') [value]  
,ROW_NUMBER() OVER(ORDER BY [value]) rr FROM #t  
CROSS APPLY STRING_SPLIT(val,',') v)  
  
SELECT * FROM #Dynamicprocessoutputresults  
UNION ALL  
SELECT '[PayerName]','[ContainerNo]','[Size]',  
'[Type]','[CHAName]','[ActGateinDate]',   
'[ContainerAgentName]','[ImporterName]',  
[1],[2],[3],[4],[5],[6],[7],[8],[9] FROM cte   
PIVOT (MAX([value]) FOR rr IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])) p

    Regards,
    Echo

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments
  2. Guoxiong 8,126 Reputation points
    2021-11-18T14:31:07.683+00:00

    Here is the updated code which dynamically casts the MAX(Column) results to string:

    DECLARE @Columns VARCHAR(MAX) = '',
        @Columns2 VARCHAR(MAX) = '',
        @Sql NVARCHAR(MAX) = '';

SELECT @Columns = STUFF((SELECT DISTINCT ',', + QUOTENAME([ActivityDescription])
  FROM #TempData FOR XML PATH ('')), 1, 1, '');

SELECT @Columns2 = STUFF((SELECT DISTINCT ',', + 'CAST(MAX(' + QUOTENAME([ActivityDescription]) + ') AS VARCHAR(20)) AS ' + QUOTENAME([ActivityDescription])
  FROM #TempData FOR XML PATH ('')), 1, 1, '');

--PRINT(@Columns2)

--DROP TABLE IF EXISTS ##Temp_Output; 
IF OBJECT_ID(N'tempdb..##Temp_Output') IS NOT NULL
BEGIN
    DROP TABLE ##Temp_Output;
END

SET @sql = '
SELECT  
    [PayerName],
    [ContainerNo],
    CAST([Size] AS VARCHAR(20)) AS [Size],
    [Type],
    CAST([CHAName] AS VARCHAR(20)) AS [CHAName],
    [ActGateinDate], 
    [ContainerAgentName],
    [ImporterName],' + 
    @Columns2 + '
INTO ##Temp_Output 
FROM (
    SELECT * 
    FROM  #TempData) AS Src
    PIVOT(MAX([Amount]) FOR [ActivityDescription] IN (' + @Columns +  ')
) pv  
GROUP BY [PayerName], [ContainerNo], [Size], [Type], [CHAName], [ActGateinDate], [ContainerAgentName], [ImporterName]
';

PRINT(@sql)
EXEC(@sql)

--DROP TABLE IF EXISTS ##Temp_Columns;
IF OBJECT_ID(N'tempdb..##Temp_Columns') IS NOT NULL
BEGIN
    DROP TABLE ##Temp_Columns;
END

SELECT [column_id], [name] 
INTO ##Temp_Columns
FROM tempdb.sys.columns 
WHERE object_id = OBJECT_ID('tempdb..##Temp_Output') 

SELECT @Columns = STUFF(
    (
        SELECT ',',+ QUOTENAME([name])
        FROM ##Temp_Columns 
        ORDER BY [column_id]
        FOR XML PATH ('')
    ),
    1,
    1,
    ''
);
--PRINT(@Columns)

--DROP TABLE IF EXISTS ##Temp_Columns_Pivot;
IF OBJECT_ID(N'tempdb..##Temp_Columns_Pivot') IS NOT NULL
BEGIN
    DROP TABLE ##Temp_Columns_Pivot;
END

SET @sql = N'
    SELECT * 
    INTO ##Temp_Columns_Pivot
    FROM (
        SELECT [name]
        FROM ##Temp_Columns 
    ) AS src
    PIVOT (
        MAX([name]) FOR [name] IN (' + @Columns +  ')
    ) AS pvt
'
EXEC(@sql);

-- Final output
SELECT * FROM ##Temp_Columns_Pivot
UNION ALL
SELECT * FROM ##Temp_Output;

--Drop temporary tables
IF OBJECT_ID(N'tempdb..##Temp_Columns') IS NOT NULL
BEGIN
    DROP TABLE ##Temp_Columns;
END
IF OBJECT_ID(N'tempdb..##Temp_Columns_Pivot') IS NOT NULL
BEGIN
    DROP TABLE ##Temp_Columns_Pivot;
END
IF OBJECT_ID(N'tempdb..##Temp_Output') IS NOT NULL
BEGIN
    DROP TABLE ##Temp_Output;
END
IF OBJECT_ID(N'tempdb..#TempData') IS NOT NULL
BEGIN
    DROP TABLE #TempData;
END
GO
    0 comments