How to add header as first row when doing pivot ?

ahmed salah 3,216 Reputation points
2021-06-21T22:25:07.56+00:00

I work on sql server 2012 I face issue I can't add extra header as first row
when make pivot
meaning add colum name as text on first row

CREATE TABLE [dbo].[getpldata](  
     [partid] [int] NOT NULL,  
     [partnumber] [nvarchar](70) NOT NULL,  
     [packageid] [int] NULL,  
     [PL] [varchar](300) NULL,  
     [Company] [varchar](150) NULL,  
     [Z2designator] [varchar](400) NULL,  
     [zlc] [int] NOT NULL,  
     [zlcStatus] [nvarchar](500) NOT NULL  
 ) ON [PRIMARY]  
      
 GO  
      
 INSERT [dbo].[getpldata] ([partid], [partnumber], [packageid], [PL], [Company], [Z2designator], [zlc], [zlcStatus]) VALUES (15783478, N'SMC-160808E-1N5S', 119281, N'Fixed Inductors', N'3L Electronic Corporation', N'0603 (1608 Metric) - 0603 (1608 Metric)-.95-1.6x0.8 - 1.6*0.8*0.8*N/R*N/R - Surface Mount', 37003, N'Active')  
 INSERT [dbo].[getpldata] ([partid], [partnumber], [packageid], [PL], [Company], [Z2designator], [zlc], [zlcStatus]) VALUES (15785228, N'SMC-160808E-4N7S', 119281, N'Fixed Inductors', N'3L Electronic Corporation', N'0603 (1608 Metric) - 0603 (1608 Metric)-.95-1.6x0.8 - 1.6*0.8*0.8*N/R*N/R - Surface Mount', 37003, N'Active')  
 INSERT [dbo].[getpldata] ([partid], [partnumber], [packageid], [PL], [Company], [Z2designator], [zlc], [zlcStatus]) VALUES (15784958, N'SMC-160808E-2N2S', 119281, N'Fixed Inductors', N'3L Electronic Corporation', N'0603 (1608 Metric) - 0603 (1608 Metric)-.95-1.6x0.8 - 1.6*0.8*0.8*N/R*N/R - Surface Mount', 37003, N'Active')  
 INSERT [dbo].[getpldata] ([partid], [partnumber], [packageid], [PL], [Company], [Z2designator], [zlc], [zlcStatus]) VALUES (15784959, N'SMC-160808E-2N7S', 119281, N'Fixed Inductors', N'3L Electronic Corporation', N'0603 (1608 Metric) - 0603 (1608 Metric)-.95-1.6x0.8 - 1.6*0.8*0.8*N/R*N/R - Surface Mount', 37003, N'Active')  
 INSERT [dbo].[getpldata] ([partid], [partnumber], [packageid], [PL], [Company], [Z2designator], [zlc], [zlcStatus]) VALUES (15785527, N'SMC-160808E-3N3S', 119281, N'Fixed Inductors', N'3L Electronic Corporation', N'0603 (1608 Metric) - 0603 (1608 Metric)-.95-1.6x0.8 - 1.6*0.8*0.8*N/R*N/R - Surface Mount', 37003, N'Active')  
 INSERT [dbo].[getpldata] ([partid], [partnumber], [packageid], [PL], [Company], [Z2designator], [zlc], [zlcStatus]) VALUES (15785528, N'SMC-160808E-R8', 119281, N'Fixed Inductors', N'3L Electronic Corporation', N'0603 (1608 Metric) - 0603 (1608 Metric)-.95-1.6x0.8 - 1.6*0.8*0.8*N/R*N/R - Surface Mount', 37009, N'Unknown')  
 INSERT [dbo].[getpldata] ([partid], [partnumber], [packageid], [PL], [Company], [Z2designator], [zlc], [zlcStatus]) VALUES (15785679, N'SMI-160808E-R15K', 119281, N'Fixed Inductors', N'3L Electronic Corporation', N'0603 (1608 Metric) - 0603 (1608 Metric)-.95-1.6x0.8 - 1.6*0.8*0.8*N/R*N/R - Surface Mount', 37003, N'Active')  
  
  
what I try as below :  
  
 DECLARE @result NVARCHAR(MAX)  
 DECLARE @col NVARCHAR(MAX)  
 DECLARE @sqldata NVARCHAR(MAX)  
 SELECT  @result = ( SELECT  STUFF(( SELECT   ',[' +  cast(zlcStatus as varchar(30)) + ']'     FROM extractreports.dbo.getpldata with(nolock)  
 group by zlc,zlcStatus  
 ORDER BY  zlc,zlcStatus asc                                   
 FOR  
 XML PATH('')  
 ), 1, 1, '') AS [Output]  )  
      
      
      
    SELECT  @col = ( SELECT   ',''' + cast(zlcStatus as varchar(30))  + ''' as ''' + QUOTENAME(zlcStatus) + ''''  
 FROM   extractreports.dbo.getpldata with(nolock)  
 group by zlc,zlcStatus  
 ORDER BY  zlc,zlcStatus asc  
 FOR  
 XML PATH('')  
 )  
      
      
 set @sqldata= '  
       
  select top 1  ''PL'' as ''PL'' ,''Company'' as ''Company'',''Z2designator'' as ''Z2designator'''  
 + @col + '  
 into extractreports.dbo.getalldata from extractreports.dbo.getpldata  
 union all  
      
 (SELECT top 999999 *   
 FROM  
 (  
 SELECT        
 [PL],  
 [Company],  
 [Z2designator],  
 cast(zlcStatus as varchar(30)) as [zlcStatus],   
 cast([PartId] as varchar(20))as [PartId]  
 FROM extractreports.dbo.getpldata  
 group by  
 [PL],  
 [Company],  
 [Z2designator],  
 cast(zlcStatus as varchar(30)),   
 cast([PartId] as varchar(20))  
 ) AS SourceTable PIVOT(count([PartId]) FOR [zlcStatus]  IN(' + @result + ')) AS PivotTable)   
 '  
 EXEC (@sqldata)  

Expected Result

107791-image.png

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,661 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-06-22T01:17:44.14+00:00

    Hi @ahmed salah ,

    Please refer below:

    DECLARE @result NVARCHAR(MAX)  
    DECLARE @result1 NVARCHAR(MAX)  
      DECLARE @col NVARCHAR(MAX)  
      DECLARE @sqldata NVARCHAR(MAX)  
      SELECT  @result = ( SELECT  STUFF(( SELECT   ',[' +  cast(zlcStatus as varchar(30)) + ']'     FROM extractreports.dbo.getpldata with(nolock)  
      group by zlc,zlcStatus  
      ORDER BY  zlc,zlcStatus asc                                   
      FOR  
      XML PATH('')  
      ), 1, 1, '') AS [Output]  )  
              
      SELECT  @result1 = ( SELECT  STUFF(( SELECT   ',cast([' +  cast(zlcStatus as varchar(30)) + '] as char(10)) '+QUOTENAME(cast(zlcStatus as varchar(30)))     FROM extractreports.dbo.getpldata with(nolock)  
     group by zlc,zlcStatus  
     ORDER BY  zlc,zlcStatus asc                                   
     FOR  
     XML PATH('')  
     ), 1, 1, '') AS [Output]  )        
              
         SELECT  @col = ( SELECT   ',''' + cast(zlcStatus as varchar(30))  + ''' as ''' + QUOTENAME(zlcStatus) + ''''  
      FROM   extractreports.dbo.getpldata with(nolock)  
      group by zlc,zlcStatus  
      ORDER BY  zlc,zlcStatus asc  
      FOR  
      XML PATH('')  
      )  
              
              
      set @sqldata= '  
               
       select top 1  ''PL'' as ''PL'' ,''Company'' as ''Company'',''Z2designator'' as ''Z2designator'''  
      + @col + '  
      into extractreports.dbo.getalldata from extractreports.dbo.getpldata  
      union all  
              
      (SELECT top 999999 [PL],[Company],[Z2designator],'+@result1+'   
      FROM  
      (  
      SELECT        
      [PL],  
      [Company],  
      [Z2designator],  
      cast(zlcStatus as varchar(30)) as [zlcStatus],   
      cast([PartId] as varchar(20))as [PartId]  
      FROM extractreports.dbo.getpldata  
      group by  
      [PL],  
      [Company],  
      [Z2designator],  
      cast(zlcStatus as varchar(30)),   
      cast([PartId] as varchar(20))  
      ) AS SourceTable PIVOT(count([PartId]) FOR [zlcStatus]  IN(' + @result + ')) AS PivotTable)   
      '  
      EXEC (@sqldata)  
      
      select * from extractreports.dbo.getalldata  
    

    Output:
    107883-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.

    0 comments No comments

0 additional answers

Sort by: Most helpful