Allow explicitly indicating grouping/on rows element in PIVOT operator
A pivoting operation is based on three elements:
- Grouping/on rows
- Spreading/on columns
- Aggregation/data
The T-SQL PIVOT operator's syntax is currently designed such that you indicate the spreading and aggregation elements explicitly, but SQL Server figures out the grouping element implicitly by elimination. That is, all columns from the operator's input table that were not specified in the aggregation and spreading elements implicitly become the grouping element.
Here's the general form of the existing syntax:
SELECT <selectlist>
FROM <sourcetable>
PIVOT( <aggregatefunction>(<aggregatecol>)
FOR <spread_col> IN(<target_cols>) ) AS <alias>;
People who are not aware of the implicit determination of the grouping element--especially those who are fairly new to T-SQL--often end up with a bug in their code. Often the initial attempts at writing PIVOT queries don't involve the use of the recommended input table expression that projects only the three relevant pivoting elements. If you just specify the aggregation and spreading elements, you end up with ALL the reset of the input table's columns as the grouping element, and get more rows than expected in the result.
A simple nonbreaking design change to the PIVOT operator's syntax can help in avoiding this common bug. Add an optional clause, say you call it ON ROWS, where you indicate the grouping/on rows element. If unspecified, then use the current implicit determination of this element.
Here's the suggested syntax:
SELECT <selectlist>
FROM <sourcetable>
PIVOT( <aggregatefunction>(<aggregatecol>)
FOR <spread_col> IN(<target_cols>)
ON ROWS <grouping_cols> ) AS <alias>;
For example, suppose that you have a table called Sales.Orders and you want to pivot the data in the table such that you get shipper IDs on rows, shipped years on columns, and the count of shipped orders per shipper and year in the data. You would use the following code to achieve this:
SELECT shipperid, [2017], [2018], [2019]
FROM Sales.Orders
CROSS APPLY( VALUES(YEAR(shippeddate)) ) AS D(shippedyear)
PIVOT( COUNT(shippeddate)
FOR shippedyear IN([2017], [2018], [2019])
ON ROWS shipperid ) AS P;
Thanks for your consideration!
Itzik Ben-Gan
shared this idea
1 comment
-
Nadir Doctor
commented
Please arrange for this enhancement at the earliest - it will be immensely useful in current supported releases as well.
Thank you.