Pivot Rows To Columns in SQL Server

How to build a pivot query in SQL Server to transform rows into columns?

Follow this tutorials and query examples on how to use the SQL Server PIVOT operator to convert rows of data into columns. The SQL Server Pivot operator allows to transpose lines to columns. But the syntax is not straightforward, especially for beginners.

Indeed, in order to work, the names of the target columns must be provided. And they must match the content of the pivoted column. This simple Pivot query example shows how to build and adapt your own query step by step. It simply moves the lines containing the months names to columns while computing the average amount of sales for each month.

Pivot queries and aggregations

In the other hand, if we don’t want to have any aggregation in the new results lines, then we need exactly one line per column created. In the example below we do a pivot with an aggregation and we use the average function.

And only the six first months of the year are used and pivoted, namely January to June. It’s easy to extend to the end of the year by adding the next 6 months. To do so, just copy/paste the data creation and query, and add the missing months.

1. SQL Server Pivot query with one fixed column

Step 1.1: Create a sample table to transpose from rows to columns

Before building the query, create the sample table with this T-SQL script, simply copy and paste it to your SSMS window.

-- If table exits, drop it
IF exists( 	SELECT 	1 FROM sys.objects
            WHERE 	object_id = object_id(N'[dbo].[SALES]') 
                AND type in (N'U') )
BEGIN 	
    DROP TABLE [dbo].[SALES]
END
GO

-- SALES table creation
CREATE table [dbo].[SALES] (
    [MONTH] NVARCHAR(20),
    [AMOUNT] NUMERIC(5)
)
GO

-- Insert first sales amount for each month
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'January', 1000)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'February', 2000)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'March', 3000)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'April', 4000)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'May', 5000)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'June', 6000)

-- Insert second sales amount for each month
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'January', 1100)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'February', 2200)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'March', 3300)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'April', 4400)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'May', 5500)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'June', 6600)

-- Check inserted data
SELECT 	*
FROM 	dbo.SALES;

The SQL Server table to pivot is presented here in lines and 2 columns are available. The goal for the next step is to have each month with an average value of the sales.

Step 1.2: Build and run the SQL Server PIVOT query

The query to pivot rows into columns is compound of these three parts, it computes the average sales per month:

1. A selection of the aggregated column and the months, each column called explicitly.
2. The sub-query with the original selection of data.
3. The PIVOT itself using the AVG aggregation function.

SELECT 	'Average SALES' AS [SALES_PER_MONTH],
        [January], [February], [March], [April], [May], [June]
FROM (
    SELECT [MONTH], [AMOUNT]
    FROM dbo.SALES
) AS SourceTable
PIVOT (
    AVG(AMOUNT)
    FOR MONTH IN ([January], [February], [March], [April], [May],[June])
) AS PivotTable;

The result of the query appears in columns after the query using PIVOT.

To perform the opposite operation, i.e., transform the columns into rows, use the SQL Server UNPIVOT operator.

2. SQL Server Pivot query multiple fixed columns

In this second example, the table to pivot has 2 fixed non numeric columns. I.e. the two columns will not be aggregated or calculated and they are also not pivoted.

Step 2.1: Script to create a table with two or more fixed columns

Hre we use the same process as in the first example. But this time, we group the data in a different way. Open SSMS and execute this query to cerate the table and insert some sample data. Indeed, we simply added the customers ID and Name as 2 new columns.

-- Create the sales table with 2 additional columns
-- The customer ID and the customer name
CREATE table [dbo].[SALES_with_Customers] (
    [MONTH]         NVARCHAR(20),
    [CUSTOMER_ID]   NVARCHAR(20),
    [CUSTOMER_NAME] NVARCHAR(20),
    [AMOUNT]        NUMERIC(5)
)
GO

-- Insert first sales amount for each month for Customer-001
INSERT dbo.SALES_with_Customers VALUES ( N'January',  N'Customer-001', N'ONE', 1000)
INSERT dbo.SALES_with_Customers VALUES ( N'February', N'Customer-001', N'ONE', 2000)
INSERT dbo.SALES_with_Customers VALUES ( N'March',    N'Customer-001', N'ONE', 3000)
INSERT dbo.SALES_with_Customers VALUES ( N'April',    N'Customer-001', N'ONE', 4000)
INSERT dbo.SALES_with_Customers VALUES ( N'May',      N'Customer-001', N'ONE', 5000)
INSERT dbo.SALES_with_Customers VALUES ( N'June',     N'Customer-001', N'ONE', 6000)
-- second step for the first customer
INSERT dbo.SALES_with_Customers VALUES ( N'January',  N'Customer-001', N'ONE', 1500)
INSERT dbo.SALES_with_Customers VALUES ( N'February', N'Customer-001', N'ONE', 1500)
INSERT dbo.SALES_with_Customers VALUES ( N'March',    N'Customer-001', N'ONE', 1500)
INSERT dbo.SALES_with_Customers VALUES ( N'April',    N'Customer-001', N'ONE', 1500)
INSERT dbo.SALES_with_Customers VALUES ( N'May',      N'Customer-001', N'ONE', 1500)
INSERT dbo.SALES_with_Customers VALUES ( N'June',     N'Customer-001', N'ONE', 1500)

-- Insert first sales amounts for each month for the Customer-002
INSERT dbo.SALES_with_Customers VALUES ( N'January',  N'Customer-002', N'TWO', 1100)
INSERT dbo.SALES_with_Customers VALUES ( N'February', N'Customer-002', N'TWO', 2200)
INSERT dbo.SALES_with_Customers VALUES ( N'March',    N'Customer-002', N'TWO', 3300)
INSERT dbo.SALES_with_Customers VALUES ( N'April',    N'Customer-002', N'TWO', 4400)
INSERT dbo.SALES_with_Customers VALUES ( N'May',      N'Customer-002', N'TWO', 5500)
INSERT dbo.SALES_with_Customers VALUES ( N'June',     N'Customer-002', N'TWO', 6600)
-- Insert second sales amount for each month for Customer-002
INSERT dbo.SALES_with_Customers VALUES ( N'January',  N'Customer-002', N'TWO', 2000)
INSERT dbo.SALES_with_Customers VALUES ( N'February', N'Customer-002', N'TWO', 2000)
INSERT dbo.SALES_with_Customers VALUES ( N'March',    N'Customer-002', N'TWO', 2000)
INSERT dbo.SALES_with_Customers VALUES ( N'April',    N'Customer-002', N'TWO', 2000)
INSERT dbo.SALES_with_Customers VALUES ( N'May',      N'Customer-002', N'TWO', 2000)
INSERT dbo.SALES_with_Customers VALUES ( N'June',     N'Customer-002', N'TWO', 2000)

-- Check inserted data
SELECT 	*
FROM 	dbo.[SALES_with_Customers];

Note that the source table contains 12 lines per Customer ID and Customer Name, for the 6 first months of the year.

2.2 Build the PIVOT query with multiple fixed columns

Once the new table is created, build the pivot query. The exact method is used here.

The only difference is that all the named columns are listed in the first select statement.

SELECT 	*
FROM (
    SELECT
	  [MONTH],
	  [CUSTOMER_ID],
	  [CUSTOMER_NAME],
	  [AMOUNT]
    FROM dbo.SALES_with_Customers
) AS Source_Table

PIVOT (
    AVG(AMOUNT)
    FOR MONTH IN ([January], [February], [March], [April], [May], [June])
) AS Pivot_Table;

Build the query using the same three steps.

1. From the source table, select all the columns to pivot and to display: Month, Customer_Id, Customer_Name and Amount
2. Configure the pivot column and the aggregation, here it’s AVG(AMOUNT)
3. Use the PIVOT operator to execute the transformation on the selected data.

Finally execute the query and check the calculated average of the sales column, displayed in the Amount column.

1. The first customer’s data is available in one unique line now.
2. Same for Customer-002.
3. Each month is now displayed in a specific dedicated column.

The SQL Server PIVOT query is useful

To finish, this T-SQL tutorial explains how to use the PIVOT operator in SQL Server with 2 step by step examples. To go further and query system tables metadata, use the MS SQL query to display the date and time of the last modification of a table.

Of course you can also do the reverse operation to transpose columns into rows using the UNPIVOT operator.

More tutorials about how to Pivot and Unpivot data

• Tutorial to Pivot Excel table columns into rows.
• How to create an Excel pivot table to analyse data?