How to pivot data using Dynamic SQL in SQL Server
Tags: sql, database, dataengineering, mssql
Published: 2023-05-22 14:54:45

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:

```sql
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:

```sql
/*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:

```sql
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];
```

![Variables' content](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3adc7zu9w3wpa0o9tm64.png)

Now let’s turn food items into columns and safe it into `#tmp` table

```sql
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;
```
![#tmp](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9nkft424p8xrmw5za4qw.png)

And now we need to create columns from our column Food. For this purpose we will use dynamic SQL.

```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

![final result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bbcexo7uvpdvsm1xfw3i.png)

If you need to keep it, you can create an additional table and insert data into it. For example:

```sql
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;
```

![final result into table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3hg2kwds5ch8ognmcc2s.png)

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.