Dynamic casting when using a hinge
I am using EAV template to store arbitrary data in one table ( Entries
). Data
is saved as nvarchar
, and through the PropertyID
target data type is bound. Now, I would like to create a view to restructure data using pivot, but I am having problems dynamically converting Data
to its intended data type. By doing the request below I am getting the 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 request:
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
It looks like the problem with CASE
is the problem, but how can I convert the query for dynamic translation to the correct data type?
source to share
Based on 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
source to share