ahmed elbarbary

ahmed elbarbary

  • 966
  • 1.5k
  • 106.1k

Conversion failed when converting the varchar value ‘Active’ to data t

Jun 21 2021 7:10 AM

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

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


Answers (1)