Sql Pivot

What is SQL PIVOT?

SQL PIVOT is a powerful and useful feature in SQL Server that allows you to transform data from rows to columns. Essentially, it rotates a table-valued expression by converting unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required.

Syntax

Here’s the basic syntax for using the PIVOT operator:

SELECT [column1], [column2],  
       [static_column1], [static_column2],  
       [pivot_column1], [pivot_column2], [pivot_columnN]  
FROM  
(
    SELECT [column1], [column2], [value_column]
    FROM [source_table]
) AS SourceTable  
PIVOT  
(
    SUM([value_column])  
    FOR [column2] IN ([pivot_column1], [pivot_column2], [pivot_columnN])
) AS PivotTable;

[column1], [column2]: Columns to be selected.
[static_column1], [static_column2]: Columns that remain static.
[value_column]: Column whose values will be aggregated.
[source_table]: Original table name.
[pivot_column1], [pivot_column2], [pivot_columnN]: Columns that will be created in the pivoted table.

When to Use SQL PIVOT

You should use SQL PIVOT when you need to:

Transform rows into columns to create a cross-tabulation report.
Summarize data to make it easier to read and analyze.
Perform data analysis in scenarios where columnar representation provides more insight.

How to Use SQL PIVOT

Let’s walk through an example to illustrate the use of SQL PIVOT:

Schema and Sample Data Query

First, let's create a sample table and insert some data.

CREATE OR REPLACE TABLE sales_data (
    product VARCHAR(50),
    region VARCHAR(50),
    sales INT
);

INSERT INTO sales_data (product, region, sales)
VALUES
('A', 'North', 100),
('A', 'South', 150),
('B', 'North', 200),
('B', 'South', 250),
('C', 'North', 300),
('C', 'South', 350);

Using SQL PIVOT

Suppose we have a sales table Sales_Data with the following data:

We want to transform this data so that we get total sales per region for each product. We can use the PIVOT operator as follows:

ELECT Product, 'North' AS north_sales, 'South' AS south_sales
FROM 
(
    SELECT Product, Region, Sales
    FROM SalesData
) AS SourceTable
PIVOT 
(
    SUM(Sales) 
    FOR Region IN ('North', 'South')
) AS PivotTable;

After executing the above query, the output will be:

Here’s what’s happening in the query:

The inner query selects the relevant columns from the SalesData table.
The PIVOT operator aggregates the Sales values and transforms the Region values (North and South) into columns.

With this structure, you'll have a comprehensive and clear blog post on SQL PIVOT. Let me know if there's anything more you need or any specific details you'd like to add!