This content originally appeared on DEV Community and was authored by Olesia
SQL Server PIVOT operator is useful when you know all values which should become columns. But if you do not know what is exactly in the table. How to turn rows into columns?
Dynamic SQL can help with it. Let’s check it on the simple example. There is a table Goods. Number of food items and stores can be unlimited. If it always had only 3 stores and 3 food items, we could use standard PIVOT.
| Store | Food | Amount |
|---|---|---|
| Store 1 | potato | 50 |
| Store 5 | potato | 150 |
| Store 12 | potato | 300 |
| Store 1 | tomato | 220 |
| Store 5 | tomato | 180 |
| Store 12 | tomato | 60 |
| Store 1 | cucumber | 500 |
| Store 5 | cucumber | 10 |
| Store 12 | cucumber | 90 |
So we need to turn food items into columns. The result should be like this:
| Store | potato | tomato | cucumber |
|---|---|---|---|
| Store 1 | 50 | 220 | 500 |
| Store 5 | 150 | 180 | 10 |
| Store 12 | 30 | 60 | 90 |
Let’s create a table and insert our data:
CREATE TABLE Goods([Store] VARCHAR(255), [Food] VARCHAR(255), [Amount] INT);
INSERT INTO Goods(Store, Food, Amount)
VALUES
('Store 1', 'potato', 50),
('Store 5', 'potato', 150),
('Store 12', 'potato', 300),
('Store 1', 'tomato', 220),
('Store 5', 'tomato', 180),
('Store 12', 'tomato', 60),
('Store 1', 'cucumber', 500),
('Store 5', 'cucumber', 10),
('Store 12', 'cucumber', 90);
GO
We need two temporary tables for calculations:
/*for turning rows into columns*/
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
CREATE TABLE #tmp(Store VARCHAR(255), Food VARCHAR(255))
GO
/*for final result*/
IF OBJECT_ID('tempdb..#result') IS NOT NULL
DROP TABLE #result
GO
Three variables:
DECLARE @sql NVARCHAR(MAX) = '' /*dynamic string*/
, @columns NVARCHAR(MAX) = STUFF(ISNULL((SELECT DISTINCT ', ['+ Food +'] INT'
FROM Goods
FOR XML PATH('')),''),1,2,'') /*column names for the final result table*/
, @summary NVARCHAR(MAX) = STUFF(ISNULL((SELECT DISTINCT ', SUM(['+ Food +']) AS ['+ Food +']'
FROM Goods
FOR XML PATH('')),''),1,2,'') /*summing amount*/
SELECT @columns AS [columns], @summary AS [summary];
Now let’s turn food items into columns and safe it into #tmp table
INSERT INTO #tmp
SELECT DISTINCT Store,
REPLACE(STUFF(ISNULL((SELECT DISTINCT ', 0 AS ['+ Food +']'
FROM Goods rw
FOR XML PATH('')),''),1,2,''),'0 AS ['+ Food +']', CAST(org.Amount AS VARCHAR) + ' AS ['+org.Food+']')
FROM Goods org;
SELECT * FROM #tmp;
And now we need to create columns from our column Food. For this purpose we will use dynamic SQL.
--create final table according to number of columns from @columns and insert data from #tmp
SET @sql = N'create table #result(food varchar(255),'+@columns+'); '+CHAR(10)+
'insert into #result '+CHAR(10);
SELECT @sql = @sql + N'select '''+Store+''' as Store, '+Food+' union all '+CHAR(10) FROM #tmp;
--remove last 'union all'
SET @sql = REVERSE(STUFF(REVERSE(@sql),1,11,''));
SET @sql = @sql + N'select food, '+@summary+' from #result group by food';
EXEC sp_executesql @sql;
The result of sp_executesql procedure will be
If you need to keep it, you can create an additional table and insert data into it. For example:
SET @sql = @sql + N'create table final_result(food varchar(255),'+@columns+');'+CHAR(10)+
'insert into final_result'+CHAR(10)+
'select food, '+@summary+' from #result group by food';
EXEC sp_executesql @sql;
SELECT* FROM final_result;
Also you can add additional columns such as total amount, total count in columns or rows when you turn data. It is quite flexible approach.
Note: if your SQL Version is higher than 2017, it is possible to use STRING_AGG() function to simplify STUFF/XML statements.
Please let me know in the comments below if you use another approaches. It would be interesting to try something new.
This content originally appeared on DEV Community and was authored by Olesia
Olesia | Sciencx (2023-05-22T14:54:45+00:00) How to pivot data using Dynamic SQL. Retrieved from https://www.scien.cx/2023/05/22/how-to-pivot-data-using-dynamic-sql/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.