SimoneF SimoneF - 5 months ago 16
SQL Question

SQL Server: flatten PIVOT result

A PIVOT function I wrote produces the following result set:

Date | User | Hour | Result | FIELD1 | FIELD2 | FIELD3 | FIELD4 | FIELD5 | FIELD6
-----------------------------------------------------------------------------------------
2015-06-23 | Pippo | 1 | OK | NULL | NULL | 10 | NULL | NULL | NULL
2015-06-23 | Pippo | 1 | OK | NULL | 5 | NULL | NULL | NULL | NULL
2015-06-23 | Pippo | 1 | OK | 1 | NULL | NULL | NULL | NULL | NULL


Is there a way, for the rows having the same Date, User, Hour, Result values to aggregate all the FIELD columns into one as following:

2015-06-23 | Pippo | 1 | OK | 1 | 5 | 10 | NULL | NULL | NULL


I have tried GROUP BY on (Date,User,Hour,Result) but the PIVOT operator keeps on disaggregating, the same holds for MAX over any of the FIELD# columns.

Any idea?

Answer

You can use your PIVOT as a subselect and consolidate your results on the main query

SELECT      Date, User, Hour, Result,
            SUM(ISNULL(Field1,0) Field1, 
            SUM(ISNULL(Field2,0) Field2, 
            ...
FROM        (   SELECT  ...
                FROM    ...
                PIVOT   ...
            )   Subquery
GROUP BY    Date, User, Hour, Result