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

Transact-SQL
, ,
1

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
table script as below :slight_smile:

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

image
image1057×41 2.81 KB

2

Seems like you've made it alot harder than necessary

select PL, Company, Z2designator, 
		sum(Case when zlcStatus = 'Active' then 1 else 0 end) as Active, 
		sum(Case when zlcStatus = 'Unknown' then 1 else 0 end) as Unknown
   FROM   #getpldata 
group by  PL, Company, Z2designator
3

i have more than 10 zlcStatus so static solution will not prefer
also i need to add extra header on my result so
are this possible
image

4

Try pivot

DECLARE @Colslist VARCHAR(MAX)  
DECLARE @Cols TABLE (Head VARCHAR(MAX))  
  
INSERT @Cols (Head)  
SELECT DISTINCT [zlcStatus]  
FROM #getpldata 
  
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'  
FROM @Cols t  
 
EXEC ('SELECT *  
FROM   
(  
        SELECT PL, Company, Z2designator, [zlcStatus]
        FROM #getpldata
) t  
PIVOT (max(zlcStatus) FOR zlcStatus IN (' + @ColsList + ')) PVT')