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

Question

Post reply

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

November 17, 2021 at 7:33 am

#3953959

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

PB_BI SSCoach Points: 17463 More actions · Answer 2

You have a couple of syntax errors regarding extra commas/strings etc.. Try the following...

F 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)

This reply was modified 3 years ago by PB_BI.
This reply was modified 3 years ago by PB_BI.

I'm on LinkedIn