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.
Top comments (4)
I am more used to Postgres and pivoting is not a standard function in it. Really cool to see another technologies thanks for this article.
PostgreSQL/MySQL/MariaDB have not built-in pivot operator and use Group By and conditional aggregation. Here Postgre Pivot example
Oh yeah thanks, actually I wrote an article about CROSSTAB in Postgre. If you have any feedback about it please do not hesitate :D
Welcome! I am glad that it was interesting and I hope it will be useful or just give an alternative idea how to pivot data.