How to pivot data using Dynamic SQL in SQL Server

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.

Comments

[Lucas Barret]

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.

[Slava Rozhnev]

PostgreSQL/MySQL/MariaDB have not built-in pivot operator and use Group By and conditional aggregation. Here Postgre Pivot example

[Lucas Barret]

Oh yeah thanks, actually I wrote an article about CROSSTAB in Postgre. If you have any feedback about it please do not hesitate :D

[Olesia Dudareva]

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.