SQL Server Pivot query rows to columns

How to use the SQL Server Pivot query to change rows to columns with the SQL built-in function ? The SQL Server Pivot query can pivot lines to columns. What is the easy 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.

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.

I 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.

Create the sample table with lines to pivot to columns

Before trying the query, create the sample table with this T-SQL script, simply copy/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 herein lines.

Run the SQL Server Query to PIVOT lines to columns

With this 3 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 go further and to do the opposite, you can use the SQL Server UNPIVOT function to transform columns to rows.

If you want more information, the SQL Server PIVOT and UNPIVOT function documentation is available here, it contains many examples.