Share via

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.
13,361 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
Accepted answer
  1. MelissaMa-MSFT 24,191 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

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest