Select one value column from many in a pivot query

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?

You need to sign in to view this answers

Related Post