Can I Create Dynamic Pivot Query From Key Value Table With Different Data Types?

I have a key-value pairs table. I have created a script using dynamic sql that pivots the table correctly. However, the keys have different data types. Is there a way to cast the k

Solution 1:

You can do this using dynamic SQL, you'll just have to crate two separate lists of "new columns" as a string. One list will include the column names being converted to your datatype, the second will be used for the PIVOT function.

The code would be:

DECLARE@colsAS NVARCHAR(MAX),
    @colsConversionAS NVARCHAR(MAX),
    @queryAS NVARCHAR(MAX)

-- get the list of [key] items for the columns used in the PIVOTselect@cols= STUFF((SELECT', '+ QUOTENAME([key])
            from yourtable
            groupby [key], datatype           
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

-- get the list of columns for the final select-- include a conversion of the columns into the correct datatypeselect@colsConversion= STUFF((SELECT', cast('+ QUOTENAME([key]) +' as '+ datatype+') as '+ QUOTENAME([key])
            from yourtable
            groupby [key], datatype           
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

-- the converted columns go in the final select list -- while the other @cols are used inside the PIVOTset@query='SELECT sampleid, '+@colsConversion+' 
            from 
            (
              select sampleid, [key], value
              from yourtable
            ) x
            pivot 
            (
               max(value)
               for [key] in ('+@cols+')
            ) p; 'exec sp_executesql @query;

See SQL Fiddle with Demo