question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked ahmedsalah-1628 commented

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


 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

107541-image.png


sql-server-generalsql-server-transact-sql
image.png (6.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ahmedsalah-1628 commented

Hi @ahmedsalah-1628,

Please refer below:

 DECLARE @result NVARCHAR(MAX)
 DECLARE @result1 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  @result1 = ( SELECT  STUFF(( SELECT   ',cast([' +  cast(zlcStatus as varchar(30)) + '] as char(10)) '+QUOTENAME(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]  )
              
 set @sqldata= '            
 SELECT top 999999 [PL],[Company],[Z2designator],'+@result1+
 ' into extractreports.dbo.getalldata '+
 '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)
        
 select * from extractreports.dbo.getalldata 

Output:
107444-output.png

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


output.png (3.4 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

can i add this row above please
select top 1 ''PL'' as ''PL'' ,''Company'' as ''Company'',''Z2designator'' as ''Z2designator'''
+ @col + '
into extractreports.dbo.getalldata from extractreports.dbo.getpldata
i need to add new header row above of inserted data
so final result i need
will be
107703-image.png


0 Votes 0 ·
image.png (7.4 KiB)

meaning i need to add header as row

0 Votes 0 ·