Introduction to SQL Server PIVOT operator

PIVOT in SQL Server operator applies a functionality used to pivot a table into a table. It requires the PivotFields object to successfully transform the information in one column into multiple columns in the output. You implement this action inside a SQL SELECT query when you have a pivot table – you change the data of one column into other columns.

• Select a base dataset for pivoting
• You can make a table or view as a temporary result by using a CTE
• Apply the PIVOT operator

Let’s apply these steps to the following example. The base data for pivoting is the production.products and production.categories tables.

SELECT 
    category_name, 
    product_id
FROM 
    production.products p
    INNER JOIN production.categories c 
        ON c.category_id = p.category_id

You can make a table or view as a temporary result by using a CTE

SELECT * FROM (
    SELECT 
        category_name, 
        product_id
    FROM 
        production.products p
        INNER JOIN production.categories c 
            ON c.category_id = p.category_id
) 

Apply the PIVOT operator

SELECT * FROM   
(
    SELECT 
        category_name, 
        product_id
    FROM 
        production.products p
        INNER JOIN production.categories c 
            ON c.category_id = p.category_id
) t 
PIVOT(
    COUNT(product_id) 
    FOR category_name IN (
        [Children Bicycles], 
        [Comfort Bicycles], 
        [Cruisers Bicycles], 
        [Cyclocross Bicycles], 
        [Electric Bikes], 
        [Mountain Bikes], 
        [Road Bikes])
) AS pivot_table;

Output:

You can add an additional column to the select list of the query which returns the base data. This will form a row group in the pivot table. Use a calculated column named “YEAR” to compute the current year and then append that to the end of your current date range.

SELECT * FROM   
(
    SELECT 
        category_name, 
        product_id,
        model_year
    FROM 
        production.products p
        INNER JOIN production.categories c 
            ON c.category_id = p.category_id
) t 
PIVOT(
    COUNT(product_id) 
    FOR category_name IN (
        [Children Bicycles], 
        [Comfort Bicycles], 
        [Cruisers Bicycles], 
        [Cyclocross Bicycles], 
        [Electric Bikes], 
        [Mountain Bikes], 
        [Road Bikes])
) AS pivot_table;

Output:

Generating column values

In this example, you need to type each of the category names in parentheses after the IN operator. If you use the QUOTENAME() function to generate the category name list, then you will be able to avoid this problem.

DECLARE 
    @columns NVARCHAR(MAX) = '';
SELECT 
    @columns += QUOTENAME(category_name) + ','
FROM 
    production.categories
ORDER BY 
    category_name;
SET @columns = LEFT(@columns, LEN(@columns) - 1);
PRINT @columns;

Output:

[Children Bicycles],
[Comfort Bicycles],
[Cruisers Bicycles],
[Cyclocross Bicycles],
[Electric Bikes],
[Mountain Bikes],
[Road Bikes]

In this snippet:

• The function QUOTENAME() formats the model category name with the square brackets, for instance, [Children Bicycles]
• The LEFT() function removes any remaining commas from the @columns string

Dynamic pivot tables

If you add a new category name to the production.categories table, you should not have to update your query. You’ll probably want to re-run this query anyway to find any other rows that now have the same category name and delete them.

I’m sure that this is a common question, so hopefully this article can answer the question for you. In order to prevent the pivot table from being built when it is not needed, you can use dynamic SQL.

In this question, instead of passing a fixed list of category names to the PIVOT operator, we construct the category name list dynamically and pass it to an SQL statement, and then execute this statement dynamically using the stored procedure sp_executesql.

DECLARE 
    @columns NVARCHAR(MAX) = '', 
    @sql     NVARCHAR(MAX) = '';

-- select the category names
SELECT 
    @columns+=QUOTENAME(category_name) + ','
FROM 
    production.categories
ORDER BY 
    category_name;

-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);

-- construct dynamic SQL
SET @sql ='
SELECT * FROM   
(
    SELECT 
        category_name, 
        model_year,
        product_id 
    FROM 
        production.products p
        INNER JOIN production.categories c 
            ON c.category_id = p.category_id
) t 
PIVOT(
    COUNT(product_id) 
    FOR category_name IN ('+ @columns +')
) AS pivot_table;';

-- execute the dynamic SQL
EXECUTE sp_executesql @sql;