'GROUP BY statement with a PIVOT command in T-SQL

I have a SQL query that involves a PIVOT command that creates the correct table but now I need to GROUP BY one of the columns.

When I try to add the GROUP BY statement, it is returning the following error: "Column 'PivotTable.1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

SELECT [Load ID],[1],[2],[3],[4]
FROM TMS_Load_Stops
PIVOT (
    MIN([Stop Zip])
    for [Sequence] IN ([1],[2],[3],[4])
    ) PivotTable
;

The original code yields the below results: enter image description here

And I would like the results to be as follows (values are random numbers for explanation purposes): enter image description here

sqlsql-servertsqlgroup-bypivot


Solution 1:[1]

If you omit the Group by clause it automatically takes [Load ID] as grouping column. We have three types of columns in pivot - Grouping column, aggregating column and spanning column. Here the grouping is [Load ID], spanning is [Sequence] and aggregating is [Stop Zip]. Use the below query.

SELECT [Load ID],[1],[2],[3],[4]
FROM TMS_Load_Stops
PIVOT (
    MIN([Stop Zip])
    for [Sequence] IN ([1],[2],[3],[4])
    ) PivotTable

Solution 2:[2]

You have to "project away" any additional column in TMS_Load_Stops before the PIVOT because it already performs grouping - using all columns not mentioned in the PIVOT:

SELECT [Load ID],[1],[2],[3],[4]
FROM (select [Load ID],[Sequence],[Stop Zip] from TMS_Load_Stops) t
PIVOT (
    MIN([Stop Zip])
    for [Sequence] IN ([1],[2],[3],[4])
    ) PivotTable
;

Solution 3:[3]

Here's an example of specifying the aggregation method for each field & realiasing:

SELECT 
    [Load ID]
    ,sum([1]) AS [1]
    ,sum([2]) AS [2]
    ,sum([3]) AS [3]
    ,sum([4]) AS [4]
FROM (select * from TMS_Load_Stops) src
PIVOT (
    MIN([Stop Zip])
    for [Sequence] IN ([1],[2],[3],[4])
    ) PivotTable
GROUP BY [Load ID]
;

In lines 3-6 we've added an aggregation method 'SUM' to sum the field records upon aggregation. This way, the interpreter knows how to aggregate when grouping with 'GROUP BY'.

^ We want to steer clear of conditional 'CASE WHEN' if at all possible since its computationally expensive. PIVOT works like expected; it unstacks the field-value relationship into their own columns.

You can also reference the pivot table as a 'common table expression' by calling it again. This way the 'GROUP BY' syntax is implicit. It would look like this:

Select * from (
    SELECT 
        [Load ID]
        ,[1]
        ,[2]
        ,[3]
        ,[4]
    FROM (select * from TMS_Load_Stops) src
    PIVOT (
        MIN([Stop Zip])
        for [Sequence] IN ([1],[2],[3],[4])
        ) PivotTable 
) AggregatedPivot
    ;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Dheerendra
Solution 2 Damien_The_Unbeliever
Solution 3

Related Questions