Conversion failed when converting the varchar value ‘active’ to data type int

Question

1.80/5 (3 votes)

See more:

, +

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 20-Jun-21 21:16pm

ahmed_sa

Updated 21-Jun-21 0:04am

Add a Solution

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

Add a Solution

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

FranzBe · Answer 1 · 2021-06-21T00:04:00

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.

Conversion failed when converting the varchar value ‘active’ to data type int

1 solution

Solution 1

Add your solution here

Preview 0

Conversion failed when converting the varchar value ‘active’ to data type int

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us