I work on SQL server 2012 when Build dynamic Query I get error
Msg 8114, Level 16, State 5, Line 16 Error converting data type varchar to float.
so How to solve this Error ?
I build dynamic query based on @Header and @column and @Body .
@Header represent header must display as Headers .
@column represent pivot columns .
@Body represent select query for data
create table #FinalTable
(
PART_ID nvarchar(50) ,
CompanyName nvarchar(50),
PartNumber nvarchar(50),
DKFeatureName nvarchar(100),
value float,
StatusId int,
DisplayOrder int,
splitFlag bit
)
insert into #FinalTable
values
('1222','Honda','silicon','package','15.50Am',2,5,0),
('1900','MERCEIS','GLASS','family','90.00Am',2,2,1),--have column per Unit on @Header because FlagAllow=1
('5000','TOYOTA','alominia','source','70.20kg',2,1,0),
('8000','MACDA','motor','parametric','50.40kg',2,3,1),--have column per Unit on @Header because FlagAllow=1
('8900','JEB','mirror','noparametric','75.35kg',2,4,0)
DECLARE @Header NVARCHAR(MAX)
SELECT
@Header = STUFF(
(
SELECT ', ' + case when A.splitFlag = 1 and a.value<> '-' and (a.Value is not null) then ''''+A.DKFeatureName +''' as '''+A.DKFeatureName+''','''+ A.DKFeatureName + 'Units' +''' as ''' + A.DKFeatureName +'Units' +'''' else ''''+A.DKFeatureName +''' as ''' + A.DKFeatureName +'''' end
FROM #FinalTable A
where StatusId=2
ORDER BY DisplayOrder
FOR XML PATH ('')
),1,2,''
)
DECLARE @Columns NVARCHAR(MAX)
SELECT
@Columns = STUFF(
(
SELECT ', ' + case when A.splitFlag = 1 and a.value<> '-' and (a.Value is not null) then '['+A.DKFeatureName+'],['+A.DKFeatureName+'Unit]' else quotename(A.DKFeatureName) end
FROM #FinalTable A where StatusId=2
ORDER BY DisplayOrder
FOR XML PATH ('')
),1,2,''
)
DECLARE @Body NVARCHAR(MAX)
SELECT
@Body = STUFF(
(
SELECT ', ' + case when A.splitFlag = 1 and a.value<> '-' and (a.Value is not null) then 'LEFT(' + QUOTENAME (A.DKFeatureName) + ',PATINDEX(''%[^0-9.]%'',' + QUOTENAME (A.DKFeatureName) + '+ ' + ''' ''' + ')-1) as ['+A.DKFeatureName+'],RIGHT('+ QUOTENAME (A.DKFeatureName) +',LEN('+ QUOTENAME (A.DKFeatureName) +') - PATINDEX(''%[^0-9.]%'','+ QUOTENAME (A.DKFeatureName) +')+1) as ['+A.DKFeatureName +'Units'+']' else quotename(A.DKFeatureName) end
FROM #FinalTable A
where StatusId=2
ORDER BY A.DisplayOrder
FOR XML PATH ('')
),1,2,''
)
DECLARE @SQL NVARCHAR(MAX)
select @SQL =CONCAT('
SELECT * Into #NewTable
FROM #FinalTable
PIVOT(max(Value) FOR DKFeatureName IN ('+@Columns+')) AS PVTTable
',
N' Select ''PART_ID'' as ''PART_ID'' ,''CompanyName'' as ''CompanyName'',''PartNumber'' as ''PartNumber'' , ' +@Header + '
union all
select PART_ID,CompanyName,PartNumber, ' +@Body + ' from #NewTable
')
EXEC (@SQL)
