Error display converting data type varchar to float?

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)

And the final product is consumed by what? Ssrs , a web page, share point?

hi

same post you put earlier !!

i replied to it !!!

what about my idea .. !!!
:slight_smile:

'15.50Am' is not a float value