Select one value column from many in a pivot query

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

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?

Well, the character '0' sorts higher than '$', so '0' is correct for the given logic. If you want to treat IsEnabled = 0 as insignificant, perhaps you want NULLIF([IsEnabled], 0) in the first part of your union. You might want similar for your PropertyValueInt column.

Or if you want the zero to be treated as an empty string (instead of null) you can try ISNULL(CAST(NULLIF([IsEnabled], 0) AS VARCHAR(50)), '''') (with quotes doubled for the dynamic SQL string).

Another approach is to drop the UNION and use a CROSS APPLY to select the appropriate source value for each row.

SET @query = 
'SELECT * FROM 
(
    SELECT  
        I.Name
        ,V.ValueColumn
        ,I.ObjectName
    FROM ItemsTable I
    CROSS APPLY (
        SELECT COALESCE(
            I.PropertyValueString,
            CAST(I.PropertyValueInt AS VARCHAR(50)),
            CAST(I.IsEnabled AS VARCHAR(50))
            ) AS ValueColumn
    ) V
) src
pivot
(
    max(ValueColumn) for Name in (' + @cols + ')
) piv'

This assumes that no more than one PropertyValue* column will be non-null per row, and IsEnabled applies only if all property values are null. You can further tweak the logic as needed.

See this db<>fiddle.