is there any possible simplification of the SQL PIVOT operator?

Question

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;

Answer 1

Thanks; I believe the answer to my question is "use Excel or some other BI tool" because there is no trivial way to do a dynamic pivot directly in T-SQL. My use case is "interactive" and "managerial," e.g. I am exploring the data, validating the data, etc. So right-click, copy with headers, paste to excel, insert pivot table...