Easy Example of SQL Pivot Rows to Columns | 2023

In SQL, the PIVOT operator is used to transform rows into columns, creating a pivot table. It allows you to rotate rows into columns based on the values in a specified column. The PIVOT operation is often used to aggregate and summarize data.

In this article, We are going to focus on the practical approach of how to transform rows into columns using SQL Pivot Rows to Columns.

Syntax of Pivot

SELECT <non-pivoted column(s)>,
    [<pivot column 1>],
    [<pivot column 2>],
    ...
    [<pivot column N>]
FROM <source table>
PIVOT (
    <aggregate function>(<aggregated column>)
    FOR <pivot column>
    IN ([<pivot value 1>], [<pivot value 2>], ..., [<pivot value N>])
) AS <alias>;

Let’s break down the syntax components:

• <non-pivoted column(s)>: These are the columns from the source table that you want to keep as they are without any aggregation or transformation.
• <pivot column 1>, <pivot column 2>, ..., <pivot column N>: These are the columns that will become the column headers in the pivot table.
• <aggregate function>: This is the aggregation function you want to apply to the <aggregated column>. Common aggregate functions include SUM, COUNT, AVG, MAX, MIN, etc.
• <aggregated column>: This is the column that you want to aggregate.
• <pivot column>: This is the column in the source table whose distinct values will become columns in the pivot table.
• <pivot value 1>, <pivot value 2>, …, <pivot value N>: These are the specific values from <pivot column> which you want to pivot into columns.
• <alias>: This is an optional alias for the pivot table.

SQL Pivot Example

Let’s take an example of a sales table in the database where the total number of sales is recorded against each month.

+---------+-------+-------+
| Product | Month | Sales |
+---------+-------+-------+
| A       | Jan   | 100   |
| A       | Feb   | 150   |
| B       | Jan   | 200   |
| B       | Feb   | 250   |
+---------+-------+-------+

To pivot the table based on the “Month” column, where the months become columns and the “Sales” values are aggregated, you can use the following SQL query.

The output of the Query Will be like this.

+---------+-----+-----+
| Product | Jan | Feb |
+---------+-----+-----+
| A       | 100 | 150 |
| B       | 200 | 250 |
+---------+-----+-----+

In the resulting pivot table, the distinct values from the “Month” column (“Jan” and “Feb”) have become columns, and the “Sales” values are aggregated using the SUM function.

Note that the specific values you want to pivot must be known in advance, as they are explicitly mentioned in the query. If you have a dynamic set of values, you may need to use dynamic SQL or alternative approaches to achieve the desired pivot functionality.

Dynamic Pivot in SQL Server

Dynamic Pivot in SQL Server allows you to pivot data with dynamic or unknown column values at runtime. It provides a flexible way to transform rows into columns based on the data present in the source table.

Example of Dynamin Pivot

DECLARE @pivot_columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- Step 1: Retrieve distinct values for pivot columns
SELECT @pivot_columns = STRING_AGG(QUOTENAME(Month), ',')
FROM (SELECT DISTINCT Month FROM Sales) AS pivot_columns;

-- Step 2: Construct dynamic SQL
SET @sql = N'SELECT Product, ' + @pivot_columns + '
             FROM (
                 SELECT Product, Month, Sales
                 FROM Sales
             ) AS Source
             PIVOT (
                 SUM(Sales)
                 FOR Month
                 IN (' + @pivot_columns + ')
             ) AS PivotTable;';

-- Step 3: Execute dynamic SQL
EXEC sp_executesql @sql;

In this example, the @pivot_columns variable stores the generated column names as a comma-separated string. The dynamic SQL statement uses this string to specify the columns for the PIVOT operation.

Note that the inner subquery (Source) is used to select the necessary columns from the source table. This is required because the PIVOT operation needs to operate on a derived table or subquery.

By executing the dynamic SQL statement using sp_executesql, the pivot query is constructed and executed at runtime, dynamically generating the columns based on the data present in the source table.

Remember to properly validate and sanitize any input used in dynamic SQL to prevent SQL injection vulnerabilities.

Dynamic Pivot in SQL Server provides the flexibility to pivot data with changing or unknown column values, allowing you to adapt your queries to different datasets and achieve more versatile data analysis and reporting.

Pivot Multiple Columns

To pivot multiple columns in SQL, you can include multiple columns in the PIVOT clause of your query. Each additional column will create additional columns in the pivot table.

Let’s consider a scenario where you have a table called “Sales” with the following structure.

+---------+-------+-------+--------+
| Product | Month | Region | Sales  |
+---------+-------+-------+--------+
| A       | Jan   | East  | 100    |
| A       | Feb   | West  | 150    |
| B       | Jan   | East  | 200    |
| B       | Feb   | West  | 250    |
+---------+-------+-------+--------+

Suppose you want to pivot the “Sales” table based on both the “Month” and “Region” columns.

SELECT Product, [Jan_East], [Jan_West], [Feb_East], [Feb_West]
FROM (
    SELECT Product, CONCAT(Month, '_', Region) AS PivotColumn, Sales
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(Sales)
    FOR PivotColumn
    IN ([Jan_East], [Jan_West], [Feb_East], [Feb_West])
) AS PivotTable;

In this example, we concatenate the “Month” and “Region” columns to form a single column called “PivotColumn” that represents the unique combinations we want to pivot.

We then include this “PivotColumn” in the PIVOT clause, along with the desired aggregate function (SUM in this case). The resulting pivot table will have columns representing the different combinations of months and regions.

+---------+----------+----------+----------+----------+
| Product | Jan_East | Jan_West | Feb_East | Feb_West |
+---------+----------+----------+----------+----------+
| A       | 100      | NULL     | NULL     | 150      |
| B       | 200      | NULL     | NULL     | 250      |
+---------+----------+----------+----------+----------+

As shown, the pivot table has columns representing each combination of month and region, and the sales values are aggregated using the SUM function.

By incorporating multiple columns in the PIVOT clause, you can pivot the data based on multiple dimensions, allowing for more comprehensive analysis and reporting.

Conclusion

In conclusion, the PIVOT operation in SQL is a powerful tool that allows you to transform rows into columns, providing a different perspective on your data. With the PIVOT operation, you can summarize, aggregate, and restructure your data based on specific criteria.

By specifying the appropriate columns, aggregate functions, and pivot values, you can generate a pivot table that consolidates information and presents it in a more concise and understandable format. This can be particularly useful for generating reports, performing data analysis, and simplifying data visualization.

---

Frequently Asked Questions