SQL Server Pivot example

How to use the pivot table sql server function ? Use the SQL Server Pivot example query to change rows to columns with the T-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.

SQL Server Pivot example query to convert rows to columns

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 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 herein lines.

Run the SQL Server PIVOT query to transform rows to columns

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.

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

To go further and query system tables metadata, use the SQL Server query to display the modification date af a table.

Display the modification date of a SQL Server table