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