Pivot Function in MSSQL

In SQL Server, the PIVOT function is used to transform rows into columns. The PIVOT function is a T-SQL operator that rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, effectively creating a cross-tabulation of the data. Here’s an example to help illustrate the concept:

Let’s say we have a table named Sales that contains the following data:

Product	Region	Sales
A	North	100
B	North	200
C	North	300
A	South	400
B	South	500
C	South	600

If we want to view the data in a cross-tabulated format where the rows represent the products, the columns represent the regions, and the cells represent the sales figures, we can use the PIVOT function.

The basic syntax of the PIVOT function in SQL Server is as follows:

SELECT <non-pivoted column>, 
    [first pivoted column] AS <column name>, 
    [second pivoted column] AS <column name>, 
    ...
    [last pivoted column] AS <column name>
FROM 
    (<source table>) 
PIVOT
(
    <aggregation function>(<value column>)
    FOR <pivoted column>
    IN ([first pivoted value], [second pivoted value], ... [last pivoted value])
) AS <alias>

Let’s use the Sales table to demonstrate the PIVOT function in SQL Server:

SELECT Product, North, South
FROM
(
    SELECT Product, Region, Sales
    FROM Sales
) AS SourceTable
PIVOT
(
    SUM(Sales)
    FOR Region IN (North, South)
) AS PivotTable;

In this example, we first select the non-pivoted column, which is Product. We then specify the pivoted columns, which are North and South, and apply the aggregation function SUM to the Sales column.

The output of this query would look like this:

Product	North	South
A	100	400
B	200	500
C	300	600

As you can see, the PIVOT function has rotated the Region column into two separate columns, North and South, and applied the SUM function to the Sales column to aggregate the sales figures for each product in each region.

In summary, the PIVOT function in SQL Server is a useful tool for transforming rows into columns and creating cross-tabulations of data. It can be used to aggregate data and make it easier to read and analyze.

If the pivot columns are unknown at the time of writing the query, you can use dynamic SQL to build the PIVOT query. Dynamic SQL allows you to construct a SQL statement as a string and execute it at runtime. Here’s an example to help illustrate the concept:

Let’s say we have a table named Sales that contains the following data:

Product	Region	Sales
A	North	100
B	North	200
C	North	300
A	South	400
B	South	500
C	South	600

And we want to PIVOT the Sales data by Region.

Here’s how you can use dynamic SQL to build the PIVOT query:

DECLARE @PivotColumns AS NVARCHAR(MAX)
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)

SELECT @PivotColumns = COALESCE(@PivotColumns + ', ', '') + QUOTENAME(Region)
FROM Sales
GROUP BY Region

SET @DynamicPivotQuery =
  N'SELECT Product, ' + @PivotColumns + '
    FROM Sales
    PIVOT (
      SUM(Sales)
      FOR Region IN (' + @PivotColumns + ')
    ) AS PivotTable'

EXEC sp_executesql @DynamicPivotQuery;

In this example, we first declare two variables, @PivotColumns and @DynamicPivotQuery, which will be used to store the list of pivot columns and the dynamic PIVOT query, respectively.

Next, we use a SELECT statement to generate the list of pivot columns by querying the Sales table and grouping by Region. The COALESCE function is used to concatenate the pivot column names into a single comma-separated string. The QUOTENAME function is used to ensure that the column names are enclosed in square brackets to prevent SQL injection attacks.

We then use the SET statement to build the dynamic PIVOT query as a string, using the @PivotColumns variable to populate the IN clause of the PIVOT operator.

Finally, we execute the dynamic SQL statement using the sp_executesql stored procedure.

This example demonstrates how to build a PIVOT query dynamically using a SQL string, which allows you to handle cases where the pivot columns are not known at the time the query is written.