[SQL] Pivot returning 0 instead of string value

I have a table in SQL Server that has rows as follows:

ID (int) Name (varchar) IsEnabled (int) ObjectName (varchar) PropertyName (varchar) PropertyValueString (varchar) PropertyValueInt
1 Rule01 1 MyObject NULL NULL NULL
2 Rule02 1 MyObject NULL NULL NULL
3 Rule03 1 MyObject NULL NULL NULL
4 Rule04 1 MyObject NULL NULL NULL
5 Rule05 1 MyObject NULL NULL NULL
6 Prop01 0 MyObject Prop01 $ NULL
7 Prop02 0 MyObject Prop02 NULL 45

I wrote this stored procedure that dynamically pivots the rows as columns based on the ObjectName parameter passed in:

DECLARE @cols AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';

SELECT
@cols = @cols + QUOTENAME(Name) + ','
FROM
(SELECT DISTINCT Name
FROM ItemsTable
WHERE LOWER(ObjectName) = LOWER(@objectName)
GROUP BY Name) AS tmp

SELECT @cols = SUBSTRING(@cols, 0, LEN(@cols))

SET @query =
'SELECT * FROM
(
SELECT
[Name]
,CAST([IsEnabled] AS VARCHAR(50)) as [ValueColumn]
,[ObjectName]
FROM ItemsTable
UNION
SELECT
[Name]
,[PropertyValueString] as [ValueColumn]
,[ObjectName]
FROM ItemsTable
UNION
SELECT
[Name]
,CAST([PropertyValueInt] AS VARCHAR(50)) as [ValueColumn]
,[ObjectName]
FROM ItemsTable
) src
pivot
(
max(ValueColumn) for Name in (' + @cols + ')
) piv'


One of the values being returned is incorrect, specifically for Prop01. I am expecting a '$' to be returned but am getting a 0.

How do I fix this error?

Continue reading...