How to Pivot Rows To Columns in SQL Server ? Simple Query

How to transform data from rows into columns using a SQL Server query?

Follow this SQL tutorial and query examples on how to use the SQL Server PIVOT operator to convert rows of data into 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.

Table of Contents

1. SQL Server 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.

2. Create a SQL Server table to transform 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.

*SQL table with data in lines before using the pivot operator*

3. Build and run the SQL Server PIVOT query with one fixed column

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

A selection of the aggregated column and the months, each column called explicitly.
The sub-query with the original selection of data.
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.

*SQL Server PIVOT query to transpose rows into columns*

About the simple Pivot query use case

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.