👀 Views: 63 💬 Answers: 1 📅 Created: 2025-09-01

sql-server t-sql pivot dynamic-sql T-SQL

I've been banging my head against this for hours. I'm converting an old project and I've been researching this but I'm sure I'm missing something obvious here, but I'm trying to create a dynamic pivot table in SQL Server 2017 to analyze sales data, but I'm encountering issues with aggregating the data correctly....... My goal is to pivot the sales data on the `Product` column to get total sales per month as the columns. I have a stored procedure that generates the pivot query dynamically based on the unique product names in the dataset. However, when I run the generated query, it only seems to return the first product's data correctly, while the other products are showing up with NULL values or incorrect totals. Here’s the query I’m generating: ```sql DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); SELECT @cols = STRING_AGG(QUOTENAME(Product), ', ') FROM (SELECT DISTINCT Product FROM SalesData) AS Products; SET @query = 'SELECT Month, ' + @cols + ' FROM (SELECT MONTH(SaleDate) AS Month, Product, SaleAmount FROM SalesData) AS SourceTable PIVOT (SUM(SaleAmount) FOR Product IN (' + @cols + ')) AS PivotTable'; EXEC sp_executesql @query; ``` However, when running this, I get results that look something like this: | Month | ProductA | ProductB | ProductC | |-------|----------|----------|----------| | 1 | 100 | NULL | NULL | | 2 | 200 | 150 | NULL | | 3 | NULL | NULL | 300 | It seems like the aggregation is not summing correctly for all products. I tried adding a GROUP BY clause within the inner query, but that didn’t help. I also verified that `SaleAmount` contains valid numeric values and that there are no filtering criteria that could omit rows. Has anyone experienced this issue? Am I missing something in the aggregation or the structure of my query? Any suggestions would be appreciated! For context: I'm using T-Sql on Windows. How would you solve this? For reference, this is a production REST API. I appreciate any insights! This is my first time working with T-Sql 3.10. What's the correct way to implement this?