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