What is Dynamic PIVOT in SQL Server?

Dynamic PIVOT is a technique in SQL that allows you to transform rows into columns dynamically, without hardcoding the column names. This is useful when the column values are not known in advance or may change over time.

In a standard PIVOT operation, you need to specify the column names explicitly. However, with a dynamic PIVOT, you use SQL's dynamic query capabilities to generate the column list dynamically based on the data in the table. This ensures flexibility and adaptability in reporting and data analysis.

Dynamic Pivot Example

Let's explore an example to gain a clearer understanding of this concept.

Let us assume, I have a table name dbo.books with few columns in it. I want to pivot the data so that categories become column headers dynamically. See the below image.

Convert rows into columns using SQL Server Pivot

Here's the Query.

DECLARE @Category AS VARCHAR(MAX)
SELECT @Category =
    COALESCE(@Category + ', ', '') + CAST(Category AS VARCHAR(20)) 
    FROM (SELECT DISTINCT Category FROM dbo.Books) Books
DECLARE @DynamicPIVOT AS VARCHAR(MAX)
SELECT @DynamicPIVOT = 'SELECT ' + @Category +
' FROM (
    SELECT Price, Category FROM dbo.Books
) Books
PIVOT (
    MAX(Price) FOR Category IN (' + @Category + ')
) Result;'

EXEC (@DynamicPIVOT)

Output

Business 
150.70Computers
125.60Programming
56.00Science
210.40

What is the difference between Dynamic PIVOT and static PIVOT?

The difference between dynamic PIVOT and static PIVOT lies in their flexibility.

Dynamic PIVOT

Column names are generated dynamically based on the dataset.

Used when column values change frequently or are not known beforehand.

It has an advantage. It automatically adjusts to new column values, making it more scalable.

Static PIVOT

Column names are explicitly defined in the query.

Used when you already know the exact columns that need to appear in the result set.

It has a limitation. If new values appear in the data, the query must be manually updated.

-------------------

Here's a list of Top 5 popular SQL Server posts.

1) How to find and remove Duplicate rows in a Table using SQL Server ROW_NUMBER() and CTE: Duplicate rows in tables can be very annoying for DBA’s and programmers, as it raises many uncomfortable questions about the authenticity of data in a database. The matter gets worse when company auditors complain about irregularities in the balance sheet etc.

2) How to convert Rows into Columns using SQL Server PIVOT OR how to use PIVOT in SQL Server: Ever wondered how you can convert data from rows to columns in SQL Server. We are talking about an SQL query, which will transform records from multiple rows into columns. Using SQL Server PIVOT, we can efficiently rotate a table’s data to show a summarized result.

3) Insert Multiple rows with a Single INSERT Statement using SQL Server Table Value Constructor: While managing an Inventory management System for an organization, I have came across a situation where I had to perform bulk upload on a table in SQL Server. Bulk upload requires inserting multiple rows of data in a table.

4) How to Convert Month Number in a Date to Month Name in SQL Server: Let us assume I have a Sales table with sales data for each month. Every day sales is stored in the table with columns such as date, quantity, price etc. I want to get the total sales for every month. Now since I have a column with “date” data type, I want to convert the month number in the date to Month Name (like February, March etc.). Find out how this is done in SQL Server.

5) SQL Server CHARINDEX Function with Examples: The primary use of an SQL Server CHARINDEX function is to find the first or starting location of an expression or characters in a given string. To make it simple, it is like searching a specified character or characters in a string.

← Previous