
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 :
- Selection of the result column and the months, each column called explicitly.
- The sub-query with the original selection of data.
- 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.
Be the first to comment