Convert Rows to Columns in SQL Server using PIVOT

What is PIVOT in SQL Server?

PIVOT is used to turn or rotate data in a table by converting unique values extracted from a single column and show the result in multiple columns.

See this image. 👇

Let's see an example.

As usual, I'll use the dbo.Books table for the example. The table has five columns and few rows in it. Create the table and insert the records in it.

🚀 Check this list of popular SQL Server post in this blog.

PIVOT Using Aggregate function

I'll PIVOT (meaning, rotate or turn) the Books table to get the max price for each distinct category. The T-SQL aggregate MAX() function will return the maximum value from a list of values in a column.

SELECT Computers, Science, Programming, Business
FROM (
  SELECT Price, Category FROM dbo.Books
) Books
PIVOT ( 
    MAX(Price) FOR Category IN (Computers, Science, Programming, Business)
) Result;

The output

Similarly, we can get the total price for each category using PIVOT and SUM() function.

SELECT Computers, Science, Programming, Business
FROM (
  SELECT Price, Category FROM dbo.Books
) Books
PIVOT (
  SUM(Price) FOR Category IN (Computers, Science, Programming, Business)
) Result;

Output

The queries above have one similarity (other than using PIVOT), which is, it uses pre-defined values as column name (Computer, Science etc.). We looked for "unique" values in "Category" column and used the list in our query as columns.

Naturally, a key question arises: How can we pivot a table when the values for our columns are unknown? Hardcoding column values is not always ideal, as categories in the Books table may change over time, and new categories could be added to the list. A more flexible approach is necessary to ensure adaptability.

SQL trick... How to Insert multiple rows with a single INSERT statement in SQL Server?

PIVOT using Dynamically extracted Columns

This may also be called as Dynamic PIVOT, where values for columns are extracted dynamically using an SQL query, at runtime.

Related: What is Dynamic PIVOT in SQL Server?

We will first extract all the Distinct categories from the table and store the result in a variable. Each value is separate by a comma.

DECLARE @Category AS VARCHAR(MAX)
SELECT @Category =
    COALESCE(@Category + ', ', '') + CAST(Category AS VARCHAR(20)) 
    FROM (SELECT DISTINCT Category FROM dbo.Books) Books
SELECT @Category Categories

Output

Category
Business, Computers, Programming, Science

We got the values for our columns, all separate by a comma. Using the above result, we will now create the Dynamic PIVOT. The syntax remains the same as we did in Static PIVOT, except that we will add the variable @Columns inside the query and execute it as we execute a Stored Procedure.

Similar example: How to Convert rows into columns and SAVE the data in a table

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 are some alternatives to PIVOT in SQL Server?

There are few alternatives to using PIVOT in SQL Server, depending on your specific needs. For example,

1) CASE Statements – You can use CASE expressions inside an aggregate function like SUM() or MAX() to manually pivot data.

Example:

Lets take the books table again. Instead of using PIVOT, you can manually pivot the data using CASE statements like this.

SELECT
    SUM(CASE WHEN Category = 'Computers' THEN Price ELSE 0 END) AS Computer_Books_Price,
    SUM(CASE WHEN Category = 'Science' THEN Price ELSE 0 END) AS Science_Books_Price
FROM dbo.Books;

Output

Computer_Books_Price 
187.80Science_Books_Price
759.05

2) Dynamic SQL – If you need a flexible solution where column names are not known in advance, dynamic SQL can generate the pivot query dynamically.

Example:

DECLARE @CategoryList AS NVARCHAR(MAX);
DECLARE @PivotQuery AS NVARCHAR(MAX);

-- Get a comma-separated list of distinct categories.
SELECT @CategoryList = STRING_AGG(Category, ',')
FROM (SELECT DISTINCT Category FROM dbo.Books) AS CategoryList;

-- Construct the dynamic pivot query.
SET @PivotQuery = 
'SELECT ' + @CategoryList + ' FROM 
(SELECT Price, Category FROM dbo.books) Books
PIVOT 
(
    SUM(Price) FOR Category IN (' + @CategoryList + ')
) AS PivotTable;'

-- Execute the query dynamically.
EXEC sp_executesql @PivotQuery

3) GROUP BY with Aggregate Functions – Instead of pivoting, you can use GROUP BY along with aggregate functions like SUM() or COUNT() to summarize data.

4) UNPIVOT for Reverse Transformation – If you need to convert columns back into rows, UNPIVOT is the counterpart to PIVOT.

The PIVOT operator is available in SQL Server 2005 and above. It simplifies the process of transforming data from rows to columns, where we use the row’s values as column headers. PIVOT usually works with aggregate functions, such as MAX(), SUM() etc.

🚀 Now if you want, you can save the PIVOT result in a Table.

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

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.

← PreviousNext →