OPEN SYMMETRIC KEY TblUserDefinedFieldKey DECRYPTION BY CERTIFICATE mDNACert
SELECT * FROM
(
SELECT u.public_id, u.instance_id, u.state, u.tag, udf.name AS [user_defined_key],
CONVERT(NVARCHAR(MAX),
DECRYPTBYKEY(value_enc)) AS [user_defined_value]
FROM tbl_user_defined_field udf
join tbl_user u on u.user_id = udf.user_id
join tbl_solution_revision sr on sr.solution_revision_id = u.solution_revision_id
join tbl_solution s on s.solution_id = sr.solution_id
WHERE
s.name = 'myName'
and u.group_id = '1234'
--and u.state = 'Active'
) as sourcetbl
PIVOT
(
max (user_defined_value) for user_defined_key in
(column1, column2, column3, column4)
) as pvt
CLOSE SYMMETRIC KEY TblUserDefinedFieldKey
Given the above query, and column4 being a date field I am trying to cast the value of that field to a readable date (the value is returned as 1543507200000
).
But doing this:
max (user_defined_value) for user_defined_key in (column1, column2, column3, cast(column4 as date) as column4)
gives a syntax error!