Conversion failed when converting the varchar value ‘Active’ to data type int
-
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 reExpected Result
-
The PIVOT in the second part of your dynamic query creates integers for the count and that is what is being evaluated first in the creation of the table by using the "INTO" clause to create the table.
The simple fix would be to split the UNION ALL query into two queries.
There are a whole lot of potential issues with what you've posted. One of the most obvious is your use of the 3 part naming convention for the table. I strongly recommend creating a 2 part named synonym that would point to the 3 part named item and use the 2 part named synonym in your code, instead.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
can you tell me how to do that please
-
Sure. Get rid of the UNION ALL and change the second part of that to an INSERT/SELECT.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply