CODEWITHSUNDEEP
sqlsql-serverpivotdynamic-sql
SoftwareDveloper
SoftwareDveloper

Reputation: 742

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?

Upvotes: 1

Views: 102

Answers (2)

T N
T N

Reputation: 10205

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.

Upvotes: 2

Luuk
Luuk

Reputation: 14958

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.

Upvotes: 0

Related Questions