Pivot tables in SQL Server allow you to transform rows into columns, making it easier to summarize and analyze data. The PIVOT operator is used to create pivot tables.

Basic Example

Let's say you have a table called Sales with the following structure:

CREATE TABLE Sales (
    SaleYear INT,
    Product VARCHAR(50),
    SalesAmount DECIMAL(10, 2)
);

INSERT INTO Sales (SaleYear, Product, SalesAmount)
VALUES
(2023, 'ProductA', 1000),
(2023, 'ProductB', 1500),
(2024, 'ProductA', 2000),
(2024, 'ProductB', 2500);

You want to create a pivot table that shows the total sales amount for each product by year. Here' how you can do it using the PIVOT operator:

SELECT * FROM (
    SELECT SaleYear, Product, SalesAmount
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(SalesAmount)
    FOR Product IN ([ProductA], [ProductB])
) AS PivotTable;

Breakdown of the Query

1. Source Table: The inner query selects the columns you want to pivot. This is the source table for the pivot operation.

2. PIVOT Operator: The PIVOT operator performs the pivoting. It aggregates the SalesAmount for each Product.

3. FOR Clause: Specifies the column you want to pivot (in this case, Product) and the values that become new columns ([ProductA], [ProductB]).

Result

The result of the pivot operation will look like this:

SaleYear | ProductA | ProductB
---------|----------|----------
2023     | 1000     | 1500
2024     | 2000     | 2500

Dynamic Pivot

If you have a dynamic number of columns to pivot, you can use dynamic SQL to generate the pivot query:

DECLARE @columns NVARCHAR(MAX), @query NVARCHAR(MAX);
SELECT @columns = STRING_AGG(QUOTENAME(Product), ', ')
FROM (SELECT DISTINCT Product FROM Sales) AS Products;

SET @query = 'SELECT SaleYear, ' + @columns + '
              FROM (
                  SELECT SaleYear, Product, SalesAmount
                  FROM Sales
              ) AS SourceTable
              PIVOT (
                  SUM(SalesAmount)
                  FOR Product IN (' + @columns + ')
              ) AS PivotTable';

EXEC sp_executesql @query;

This approach dynamically generates the list of products and the pivot query, making it adaptable to any number of products.

Pivot tables are powerful for summarizing and analyzing data, providing insights into trends and patterns. If you have a specific scenario or dataset in mind, feel free to share, and I can help tailor the pivot table to your needs!