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

- - Source

Answers

answered 1 week ago Dheerendra #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

answered 1 week ago Gordon Linoff #2

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
;

answered 1 week ago Damien_The_Unbeliever #3

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
;

comments powered by Disqus