How to Use SQL Pivot in SQL Server

The SQL PIVOT operator is a powerful tool in SQL Server for transforming data from rows to columns, making it more readable and easier to analyze. In this tutorial, we will explore how to use the PIVOT operator and its practical applications in SQL queries.

What is Pivoting?

PIVOT is used to convert rows into columns. It allows you to summarize data from a database in a more readable format. It’s often used for creating reports where the same data needs to be displayed in different orientations. For example, you may want to display sales data by months or convert a list of products and their corresponding sales into a more compact table.

Basic Syntax of Pivot

The basic syntax for the SQL Pivot operator is as follows:

SELECT , , 
FROM (SELECT , ,  FROM ) AS SourceTable
PIVOT (SUM() FOR  IN ([, , ])) AS PivotTable;

In the above query:

• , , are the columns you want to select.
• is the column with the data you want to aggregate.
• is the column you want to pivot on.
• , , are the values you want to create columns for.

Example: Pivoting Sales Data

Let’s take a look at an example where we have a sales table that records monthly sales for each product.

CREATE TABLE Sales
(
    Product VARCHAR(50),
    Month VARCHAR(20),
    SalesAmount INT
);

INSERT INTO Sales VALUES ('Product A', 'January', 500);
INSERT INTO Sales VALUES ('Product A', 'February', 600);
INSERT INTO Sales VALUES ('Product B', 'January', 400);
INSERT INTO Sales VALUES ('Product B', 'February', 300);

Now, let’s use the PIVOT operator to convert the monthly sales data into a columnar format, summarizing the sales by product for each month:

SELECT Product, [January], [February]
FROM (SELECT Product, Month, SalesAmount FROM Sales) AS SourceTable
PIVOT (SUM(SalesAmount) FOR Month IN ([January], [February])) AS PivotTable;

Output

The result of the pivoted query would look like this:

This output is much more readable and easier to analyze compared to the original row-based format.

Conclusion

SQL’s PIVOT operator is a useful tool for transforming data into a more readable and organized structure. By following the simple syntax and applying it to real-world data, you can effectively summarize and present your database results in a more compact form. Whether you're dealing with sales data, performance metrics, or financial reports, PIVOT can significantly improve your data presentation.