Dynamic PIVOT with SQL Server

Hello and welcome back to LearningSQL with Beno!

In my last post, we learned how to use PIVOT and UNPIVOT statements to change rows to columns and columns to rows. This is a great tool when we need to do this kind of analysis of our data.

However, I did mention that it was a little annoying that we needed to hard-code the name of the columns we wanted to show. Like in our last example where we had the Years as columns.

What will happen when year 2020 data becomes available? We would need to go to the stored procedure, query or whatever, and manually include the new year there.

GO DYNAMIC!

Of course there is a way to avoid all this hustle and keep our queries more maintainable and with less stress. I’m talking about Dynamic SQL.

For those who don’t know what that is, the simple explanation would be:

Code built on the fly.

Well yeah, that is what Dynamic SQL basically is. We’re going to use the code we already know and find a way to generate more code to manage scenarios where we don’t know how’s the data is going to change.

I will not go into much detail on Dynamic SQL, I’ll leave that for another time. In the meantime, let’s start working on our example and how to make it dynamic, so when a new year is entered into the transactional data, it displays the information correctly in our tabular report.

Let’s first start with the code we did for un-pivoting data, if you don’t have it, you can download it from here.

We already had our data pivoted, then we unpivoted and now we’re going to insert it into a new table to add more data and see how everything is dynamically converted to columns. You’ll see, trust me. This is going to work. 😉

-- Changing columns to rows
SELECT 
	Company		-- This is the column we already had.
,	[Year]		-- We're creating this column
,	[Revenue]	-- And this column from the values we are unpivoting.
INTO CompanyDataTransactions -- Insert into a new table.
FROM
	CompanyData
	UNPIVOT
	(
		-- Name we are giving this name to the column we're creating.
		[Revenue]
		-- Set of columns that we are unpivoting
		FOR [Year] IN ([2012],[2013],[2014],[2015],[2016],[2017],[2018],[2019])
	) AS u;

Now that we have our transactional data, let’s add more rows. I added the year 2011 and 2020 for all the companies.

First, we need to identify the values that will go in columns. In this case it’s Year. OK, now we need something to store our distinct list of years. I’m going to create a variable and make use of a new function of SQL Server, STRING_AGG() to make it a comma separated list.

DECLARE @columns NVARCHAR(1000);
-- Create a list of distinct values that will become our columns.
SELECT @columns = STRING_AGG([Year], ',') FROM
(
	SELECT DISTINCT QUOTENAME([Year]) AS [Year] 
	FROM CompanyDataTransactions
) C;

That’s done. Now we need to write our PIVOT statement the same way we always do, except this time we will declare a VARCHAR(MAX) variable and assign our query.

DECLARE @pivot_stmt VARCHAR(MAX);
SET @pivot_stmt = 
'
SELECT
	*
FROM
(
	SELECT
		Company
	,	[Year]
	,	Revenue
	FROM
		CompanyDataTransactions
) AS CompanyData
PIVOT
(
	MAX(Revenue)
	FOR [Year] IN ('

Look at how I ended the sentence, right in the opening parenthesis, this is because here’s where we are going to include our columns. We’re almost done. We just need a couple more things to do. Let’s continue.

After we did everything above, we just need to add the columns to our dynamic script and execute it.

-- Add the colums in our list
SELECT @pivot_stmt = @pivot_stmt + (SELECT @columns) + ')) AS P;'
EXEC sp_executesql @pivot_stmt

And we’re done! Let’s see the results of the dynamic query.

As you can see, we now have years 2011 and 2020 added to our report. But, what happens if you have an older version of SQL Server and you cannot use STRING_AGG()?

Well, there another alternative where instead of creating a variable to store your distinct values, you can create either a table variable, a temporary table or doing a SELECT DISTINCT directly in the part where the columns go. I’ll show you.

-- Using a table variable
DECLARE @columns TABLE
(
	column_name NVARCHAR(200)
);
-- Create a list of distinct values that will become our columns.
INSERT INTO @columns
SELECT DISTINCT [Year] AS [Year]
FROM CompanyDataTransactions;

Here’s our first change. We need to use a table variable and insert the distinct values there. Next:

-- Add the colums as a comma separated list.SELECT @pivot_stmt = @pivot_stmt + '[' + column_name + '],'
FROM @columns;

-- We need to remove the last comma from the list.SELECT @pivot_stmt = SUBSTRING(@pivot_stmt, 1, LEN(@pivot_stmt)-1); 
SELECT @pivot_stmt = @pivot_stmt + ')) AS P;'

Here’s a little tricky. See how we assign the column names to the @pivot_stmt variable and surround them by square brackets and a comma. The thing here is that you need to remove the last comma, otherwise it will throw an error message. The same thing happens when you use a temporary table.

Let’s see the other alternative.

-- Add the colums as a comma separated list querying directly from the table.
SELECT @pivot_stmt = @pivot_stmt + '[' + [Year] + '],' FROM CompanyDataTransactions GROUP BY [Year];

-- We need to remove the last comma from the list.
SELECT @pivot_stmt = SUBSTRING(@pivot_stmt, 1, LEN(@pivot_stmt)-1); 
SELECT @pivot_stmt = @pivot_stmt + ')) AS P;'
EXEC sp_executesql @pivot_stmt;

We needed to GROUP BY [Year] here, and this is because we have duplicate values and, by grouping them we can get just one value from there since DISTINCT doesn’t work in this scenario.

There you go. Three different ways of how to dynamically turn columns into rows. This way we’re going to be able to see any new year or company added to the table.

I hope you liked the post and that you’ve learned something new or refreshed some old knowledge.

Also, remember that you can find the code for this and other posts in the following link: https://github.com/learningsql/sql-code

If you have any questions or comments, shoot me an email, leave a message or tweet me here.

Don’t forget to like and share. See you soon. Cheers!

If you want to receive updates and new blog posts to your inbox, subscribe to my LearningSQL with Beno email list down here.

[mc4wp_form id=”331″]