When building a dynamic query, an error is shown converting data type varchar to float - how to solve it?

Question

I work on SQL Server 2012; when building a dynamic query, I get this error

Msg 8114, Level 16, State 5, Line 16
Error converting data type varchar to float

How to solve this error?

I build a dynamic query based on @Header and @column and @Body.

@Header represents the 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)

Answer 1

The column value you are using is float which is a number not varchar. But you input varchar = "15.50Am".

You can create new columns for units (am, kg, etc.)

or you can change value like value numeric (5,2)