Click here to Skip to main content
15,035,600 members
Please Sign up or sign in to vote.
1.80/5 (3 votes)
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
SQL
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')

expected result

SQL
PL	Company	Z2designator	   Active	Unknown
Fixed Inductors	3L Electronic Corporation	0603 (1608 Metric) - 0603 (1608 Metric)-.95-1.6x0.8 - 1.6*0.8*0.8*N/R*N/R - Surface Mount	6	      1


What I have tried:

SQL
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)
Posted
Updated 21-Jun-21 0:04am
Comments
Richard MacCutchan 21-Jun-21 4:32am
   
"Conversion failed when converting the varchar value 'Active' to data type int."
Exactly what value do you think that word could represent?
ahmed_sa 21-Jun-21 5:12am
   
i already do cast so why it display like that
Richard MacCutchan 21-Jun-21 6:33am
   
You cannot cast a string to an int, they are totally different objects. Think about it: you go into a shop and ask the price of a mobile phone, and the shop assistant says, "Expansion".

1 solution

The Conversion failure comes from the one record in table getalldata that has a value of string 'active' in column '[Active]'
If you expand your generated sql an perform it step by step
SELECT TOP 1 'PL' AS 'PL'
	,'Company' AS 'Company'
	,'Z2designator' AS 'Z2designator'
	,'Active' AS '[Active]'
	,'Unknown' AS '[Unknown]'
INTO getalldata
FROM getpldata

select * from getalldata

you now see
PL	Company	Z2designator	[Active]	[Unknown]
PL	Company	Z2designator	Active	Unknown

now the UNION ALL faces a string coming from the first command with an int coming from the second command.

I did not understand why you need the first command at all. The second select command gives you the expected result you are looking for

SELECT TOP 999999 *
	FROM (
		SELECT [PL]
			,[Company]
			,[Z2designator]
			,cast(zlcStatus AS VARCHAR(30)) AS [zlcStatus]
			,cast([PartId] AS VARCHAR(20)) AS [PartId]
		FROM getpldata
		GROUP BY [PL]
			,[Company]
			,[Z2designator]
			,cast(zlcStatus AS VARCHAR(30))
			,cast([PartId] AS VARCHAR(20))
		) AS SourceTable
	PIVOT(count([PartId]) FOR [zlcStatus] IN (
				[Active]
				,[Unknown]
				)) AS PivotTable



btw, I upvoted your question for providing create table and sample data statements.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900