Dynamic casting when using pivot

Question

I am using EAV pattern to store arbitrary data in a single table (Entries). Data is stored as nvarchar and via PropertyID a target data type is linked. Now I'd like to create a view to restructure the data using pivot, but I am having trouble to dynamically cast Data to its intended data type. Executing the query below, I get error #8114 error converting data type nvarchar to float.

This is essentially my database structure:

EntryID | PropertyID | Data (nvarchar)
----------------------------
1       | 1          | 1
2       | 2          | abc
3       | 3          | 2.0
....


PropertyID | PropertyName | TypeID
------------------------------------
1          | intProp      | 1
2          | strProp      | 2
3          | fltProp      | 3
....

TypeID | TypeName
-----------------
1      | int
2      | string
3      | float
4      | bool
5      | datetime
....

and this is the query:

SELECT [intProp], [strProp], [fltProp]
FROM
(
    SELECT e.EntryID, p.PropertyName, 

        CASE 
            WHEN t.TypeName = 'int' THEN
                CAST(e.data as int)
            WHEN t.TypeName = 'float' THEN
                CAST(e.data as float)
            WHEN t.TypeName = 'string' THEN
                e.data

        END as converted

    FROM 
        Entries e 
        INNER JOIN Properties p ON e.PropertyID = p.PropertyID
        INNER JOIN Types t ON p.TypeID = t.TypeID           
) as t1
PIVOT
(
    MAX(converted)
    FOR PropertyName IN ( [intProp], [strProp], [fltProp])
) as piv

So apparently the CASE statement is the problem, but how can I transform the query to dynamically cast to the correct data type?

Answer 1

Based from my previous comment, I tried to create a SQL Script that might help you:

    DECLARE @colsConversion as NVARCHAR(MAX), @query AS NVARCHAR(MAX)
    SELECT @colsConversion = STUFF((SELECT ',CAST('+QuoteName(p.PropertyName)+' as '+t.TypeName+') as '+QuoteName(p.PropertyName)
    FROM  Properties  p 
    INNER JOIN Types t ON p.TypeID = t.TypeID
    GROUP BY p.PropertyName, t.TypeName
    FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'')



SET @query = 'SELECT ' + @colsConversion +'
FROM
(
    SELECT e.EntryID,p.PropertyName,t.TypeName
    ,e.Data
    FROM Entries e
    INNER JOIN Properties p ON e.PropertyID = p.PropertyID
    INNER JOIN Types t ON p.TypeID = t.TypeID
)AS T1
PIVOT
(
    MAX(Data)
    FOR PropertyName in ([intProp],[strProp],[fltProp])
)as piv'

exec sp_executesql @query