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

The table before the pivot, the months are in lines.
The table before the pivot, the months are in lines.

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.

SQL Server PIVOT query
SQL Server PIVOT query with the results in columns.

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

2. SQL Server PIVOT query with multiple fixed columns

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.

Create the table to pivot in SSMS with multiple fixed columns
Create the table to pivot in SSMS with multiple fixed columns

2.2 Build the query with multiple columns to pivot

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.
SQL Server PIVOT query with multiple columns explained step by step
SQL Server PIVOT query explained

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.
SSMS screenshot of the table before and after the PIVOT of the rows into columns
SSMS screenshot of the table before and after the PIVOT of the rows into columns

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.

MS Tutorials about Pivoting and Unpivoting data

Be the first to comment

Leave a Reply

Your email address will not be published.


*