How to use the SQL Server PIVOT query to convert rows to columns ?

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

How to use the SQL Server PIVOT operator in a query and convert the rows of data into columns? The SQL Server Pivot query allows pivot lines to columns. What is the syntax of a PIVOT query? The PIVOT function is pivoting the lines into columns, in order to work, provide the names of the target columns. We create the columns, and they must match the content of the pivoted column. 

This simple SQL Server Pivot query shows how to build your PIVOT query step by step and moves the lines containing the months names to columns while computing the average amount of sales for each month.

1. PIVOT query in SQL Server without fixed column

Indeed, if we don’t want to have any aggregation in the new results lines, then we need exactly one line per column created, in this example we do a pivot with an aggregation and we use the average function. In this example only the six first months of the year are shown and pivoted, namely January to June, it’s easy to project to the end of the year by simply adding the next months. To do so, just copy/paste the data creation and query, and add the missing months.

1.1 Create a sample table to convert from rows to columns

Before trying 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, 2 columns are available. The goal for the next step is to have each month with an average value of the sale.

1.2 Build and run the SQL Server PIVOT query

With this three steps, the pivot query computes the average sales per month:

1. Selection of the result column and the months, each column called explicitly.
2. The sub-query with the original selection of data.
3. The PIVOT() itself with the AVG() 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.

SQL Server UNPIVOT

2. SQL Server PIVOT query with 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.

2.1 Create a table with two or more columns to PIVOT

Same process as the first example, this time, the principle is very similar, but we group the data in a different way. Open SSMS and execute this query to generate the sample data.

-- 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 line per Customer ID and Customer Name, for the 6 first months of the year.

2.2 Build the PIVOT query with multiple columns

Once the new table is created, build the 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 amounts and the average of the sales 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 one column.

Conclusion on the SQL Server PIVOT query

To finish, this T-SQL tutorial explains how to use the PIVOT operator in SQL Server with 2 step by step examples. If you want more information about the SQL Server PIVOT and UNPIVOT function, the official documentation is available, it contains many more 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.

More MS technical tutorials about Pivoting and Unpivoting data

• How to use the UNPIVOT operator in SQL Server?
• Tutorial to Pivot Excel table columns into rows.
• How to create an Excel pivot table to analyze data?

Modification date of SQL Server tables and views with a T-SQL query