CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-bypivot
C1-14MP
C1-14MP

Reputation: 97

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

Upvotes: 4

Views: 10724

Answers (4)

jameshollisandrew
jameshollisandrew

Reputation: 1331

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
    ;

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

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
;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269943

Use conditional aggregation. It is much simpler:

SELECT [Load ID],
       MIN(CASE WHEN [Sequence] = 1 THEN [Stop Zip] END) as [1],
       MIN(CASE WHEN [Sequence] = 2 THEN [Stop Zip] END) as [2],
       MIN(CASE WHEN [Sequence] = 3 THEN [Stop Zip] END) as [3],
       MIN(CASE WHEN [Sequence] = 4 THEN [Stop Zip] END) as [4],
FROM TMS_Load_Stops
GROUP BY [Load ID];

The PIVOT doesn't work because you have additional columns in the table being pivoted. I just don't like the syntax or how it works, but you can also fix it by only selecting the columns you need:

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

Upvotes: 1

Dheerendra
Dheerendra

Reputation: 308

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

Upvotes: 4

Related Questions