SQLTeam.com | Weblogs | Forums

How to add customer extra header row on top of pivot?

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 :slight_smile:

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