Select one value column from many in a pivot query

Question

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?

Please write meaningful titles, as directed to in How to Ask; your title is just 3 tags which is what the tags are for. — Thom A
– Thom A ♦, Commented Oct 25, 2024 at 20:21
Also, you ask three questions here; questions should contain one question. If you have several questions, ask several distinct questions. — Thom A
– Thom A ♦, Commented Oct 25, 2024 at 20:22
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. See this db<>fiddle. — T N
– T N, Commented Oct 25, 2024 at 20:41
"Query is returning an incorrect value" No, you are writing the query wrong that is returning the wrong value. — Eric
– Eric, Commented Oct 25, 2024 at 21:06
Another approach is to drop the UNION and use a CROSS APPLY to select the appropriate source value for each row - ...FROM ItemsTable CROSS APPLY (SELECT COALESCE(PropertyValueString, CAST(PropertyValueInt AS VARCHAR(50)), CAST(IsEnabled AS VARCHAR(50))) AS ValueColumn) V .... 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. See this db<>fiddle. You can further tweak the logic as needed. — T N
– T N, Commented Oct 25, 2024 at 21:16

T N · Accepted Answer · 2024-11-03 02:01:02Z

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.

Luuk · Accepted Answer · 2024-10-26 12:07:02Z

0

Another option, without using PIVOT is:

SELECT
  ObjectName,
  MAX(PropertyName) as Prop01,
  MAX(PropertyValueString) as Prop02,
  SUM(CASE WHEN Name='Rule01' THEN IsEnabled ELSE 0 END) as Rule01,
  SUM(CASE WHEN Name='Rule02' THEN IsEnabled ELSE 0 END) as Rule02,
  SUM(CASE WHEN Name='Rule03' THEN IsEnabled ELSE 0 END) as Rule03,
  SUM(CASE WHEN Name='Rule04' THEN IsEnabled ELSE 0 END) as Rule04,
  SUM(CASE WHEN Name='Rule05' THEN IsEnabled ELSE 0 END) as Rule05
FROM itemsTable
GROUP BY ObjectName

see: DBFIDDLE

P.S. SUM(), or MAX(), can be changed to another aggregate function is needed.

answered Oct 26, 2024 at 12:07

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

Collectives™ on Stack Overflow

Select one value column from many in a pivot query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related