I’m working with a database table that has 3 fields: region, order_date, and sales_total. This table stores multiple sales records for each day.
What I really want is to write something clean like:
select order_date, sum(sales_total) pivot by region;
This would give me one row for each date with separate columns for every region showing the total sales for that specific day.
But instead, I need to build complex subqueries or use dynamic SQL to handle all the possible region values. I know I could use AI tools to generate this code, but I’m wondering if there’s a more straightforward approach in SQL Server that’s closer to my simple example above?
Right now I have to use something complex like this:
-- Variables for building dynamic query
DECLARE @region_list NVARCHAR(MAX), @query NVARCHAR(MAX);
-- Build list of all regions
SET @region_list = STUFF((
SELECT DISTINCT ',' + QUOTENAME(region)
FROM Orders
FOR XML PATH('')
), 1, 1, '');
-- Create the full query
SET @query = '
SELECT [order_date], ' + @region_list + '
FROM (
SELECT [order_date], [region], [sales_total]
FROM Orders
) AS DataSource
PIVOT (
SUM(sales_total)
FOR region IN (' + @region_list + ')
) AS PivotResult;';
-- Run the query
EXEC sp_executesql @query;
This works but feels like overkill for such a basic request. Is there a simpler way to pivot data without all this dynamic SQL complexity?