Conditional aggregation referencing table instead of manual entry

Question

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.

you should use a dynamic pivot.[This question](http://stackoverflow.com/questions/18657214/sql-server-dynamic-pivot-over-5-columns?rq=1) might help you. — Vamsi Prabhala, Jun 29 '16 at 20:21

JamieD77 · Accepted Answer · 2016-06-29T21:21:13.730

1

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, '')

edited Jun 29 '16 at 21:21

answered Jun 29 '16 at 20:44

JamieD77

13,796
1
17
27

Nice, this appears to do what I want. However, I have a table `TEST` with the descriptions of these columns instead of values. I tried joining this test table in the first SELECT statement to see if I can get the column description instead, but got an error...any ideas how I can join this `TEST` table? – Peter Jun 29 '16 at 21:02
can you show my what you have now? or at least the table schemas – JamieD77 Jun 29 '16 at 21:11
The result has the columns Item, W, X, Y, Z. My `TEST` table has 2 columns: Column, Description. Column has 4 rows, W, X, Y, Z and Description has 4 rows, Description1, Description2, Description3, Description4. I'm trying to get the resulting table to show the Description in the column instead of W, X, Y, Z in the original query. – Peter Jun 29 '16 at 21:16
1

you might be able to just change the @Columns query to this.. `SELECT @Columns = STUFF(( SELECT ',' + CONCAT(QUOTENAME([Description]), ' = MAX(CASE WHEN [Columns] = ''', [Columns], ''' THEN Result END)') FROM TEST FOR XML PATH('') ), 1, 1, '')` – JamieD77 Jun 29 '16 at 21:19
Awesome! Thank you! – Peter Jun 29 '16 at 21:27

Conditional aggregation referencing table instead of manual entry

1 Answers1