Peter Peter - 5 months ago 12
SQL Question

Conditional aggregation referencing table instead of manual entry

I asked a question previously on conditional aggregation. SQL Transform Crosstab Pivot and the answer was great! However, I need to expand on this.

The solution at the time was the following query:

SELECT
Item,
[X] = MAX(CASE WHEN [Columns] = 'X' THEN Result END),
[Y] = MAX(CASE WHEN [Columns] = 'Y' THEN Result END),
[Z] = MAX(CASE WHEN [Columns] = 'Z' THEN Result END)
FROM thisTable
GROUP BY Item


Question: I now have a table
TEST
with W, X, Y, Z in rows. Instead of adding another line (
[W] = MAX(CASE WHEN [Columns] = 'W' THEN Result END)
) to the query, can the query above be re-written to refer to the table for the values?

The reason is because I anticipate that there will more than just W, X, Y, Z and want to avoid adding more lines to this query in the future.

Answer

if you want to stay with conditional aggregation you can create your query dynamically like this.

DECLARE @Columns NVARCHAR(MAX),
        @Sql NVARCHAR(MAX)

SELECT  @Columns = STUFF((
    SELECT  ',' + CONCAT(QUOTENAME([Columns]), ' = MAX(CASE WHEN [Columns] = ''', [Columns], ''' THEN Result END)')
    FROM    thisTable
    GROUP BY [Columns] -- distinct
    ORDER BY [Columns] -- order column names
    FOR XML PATH('')
), 1, 1, '')

SET @Sql = N'
    SELECT  Item,
            ' + @Columns + '
    FROM    thisTable
    GROUP BY Item
'
EXEC(@Sql)

if you want to use DESCRIPTION from TEST as column name you could use this.

SELECT  @Columns = STUFF((
    SELECT  ',' + CONCAT(QUOTENAME([Description]), ' = MAX(CASE WHEN [Columns] = ''', [Columns], ''' THEN Result END)')
    FROM    TEST
    FOR XML PATH('')
), 1, 1, '')
Comments