Conversion failed when converting the varchar value 'Active' to data type int.

Question

I work on sql server 2012 I face issue when make pivot for data I get error
Conversion failed when converting the varchar value 'Active' to data type int.
so how to solve this issue please

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

Answer 1

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]  )  
            
set @sqldata= '              
SELECT top 999999 [PL],[Company],[Z2designator],'+@result1+  
' into extractreports.dbo.getalldata '+  
'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:

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.