DEV Community

Olesia Dudareva
Olesia Dudareva

Posted on • Updated on

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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];
Enter fullscreen mode Exit fullscreen mode

Variables' content

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;
Enter fullscreen mode Exit fullscreen mode

#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;
Enter fullscreen mode Exit fullscreen mode

The result of sp_executesql procedure will be

final result

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;
Enter fullscreen mode Exit fullscreen mode

final result into table

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)

Collapse
 
yet_anotherdev profile image
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.

Collapse
 
rozhnev profile image
Slava Rozhnev

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

Collapse
 
yet_anotherdev profile image
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

Collapse
 
notte profile image
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.