The pivot operator is a common requirement for querying data, but requires a cumbersome syntax that I always have to look up and usually find it easier just to copy from SSMS and paste into Excel to get a quick answer, so I have never bothered to memorize the syntax. I’m not new to this; I have worked around this for decades, but wanted to ask community if there was something I missed…
Assume I have a table with just 3 columns: state, date, amount
representing many individual amounts per day being recorded.
I want to write a query like this:
select date, sum(amount) pivot by state;
to produce output that has one row per date, and a column for every state with the sum of all the revenue_amounts for that day.
Instead, I have to create a subquery, figure out what all possible values of state are and code those in or use dynamic sql. Yes, I can ask chatgpt. But I am curious, for this common, trivial query, (using Microsoft Transact-SQL) is there some way to write it close to the simple line I wrote above?
I can achieve the desired outcome with something like this code, but am too lazy to type this much and troubleshoot it when I want something I can type and get results in 5 seconds:
— Declare variables for dynamic SQL
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
— Get the distinct state names and format them for the PIVOT clause
SET @columns = STUFF((
SELECT DISTINCT ‘,’ + QUOTENAME(state)
FROM Sales
FOR XML PATH(”)
), 1, 1, ”, ”);
— Build the dynamic SQL query
SET @sql = ‘
SELECT [date], ‘ + @columns + ‘
FROM (
SELECT [date], [state], [amount]
FROM Sales
) AS SourceTable
PIVOT (
SUM(amount)
FOR state IN (‘ + @columns + ‘)
) AS PivotTable;’;
— Execute the dynamic SQL
EXEC sp_executesql @sql;
David Atkins is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
The query won’t write for you, nor does it know what is in your data. The closest thing would be to create a sql script or SP that dynamically generated the query as output and just always use that.
A more simplistic approach from a syntax standpoint that I often see people take is to SUM CASE expressions instead of using PIVOT. This yields the same outcome; however you need to know what you are interested in aggregating or else use something to automate the unknown, mentioned above.
SELECT
SUM(CASE WHEN State = 'TX' THEN Value ELSE 0 END) AS TX,
SUM(CASE WHEN State = 'NY' THEN Value ELSE 0 END) AS NY,
SUM(CASE WHEN State = 'FL' THEN Value ELSE 0 END) AS FL
FROM
Table