How to create PIVOT table in SQL Server?
How to create PIVOT table in SQL Server?
493
19-Dec-2023
Updated on 23-Jan-2025
Khushi Singh
23-Jan-2025PIVOToperation is used in generatingPIVOTtable in SQL server to change the rows in a table and turn them to columns of the same table for easy analyze and summate data. This is very useful when you need to reorganize data for purposes of reporting or comparison. First of all, your data should be presented in the way where one column gives values which would transform into new columns, the second column contains values that would be summarized, and the third and further columns offer context or buckets for the first two columns.It starts with a base query by which the raw data is requested from the server. For instance, using the Sales table that consists of columns such as Product, Month, and Sales Amount, the initial query would retrieve these fields. After this, you define the
PIVOT operation. This means defining an aggregate function such asSUMorCOUNT, identifying the column’s values to become the new columns (such as Month) and listing the customized values in that column for instance, ‘Jan’, ‘Feb’, ‘Mar’.Specifically, the
PIVOT operatorsyntax demands that the subquery servers as the source data. Here again in this subquery you only pickup the columns required for the transformation stage. ThePIVOTblock then performs the aggregation and convert the row values to columns depending on the values which are indicated in theIN clause. For instance, when using monthly data and pivoting sales on a month basis then, the data output obtained would show the total sales for each product for the listed months.This creates a table where each row corresponds to a product type, while Columns to the right represent pivoted columns with total quantity amount or any other hierarchical value for each month. SQL Server, for example, fills missing values in some or all columns with
NULL, which is then changed with a default value of, for instance, 0 as provided byCOALESCEorISNULLfunctions. Unlike the basicPIVOTtable which needs the user to specify the new column, you can use dynamical SQL when the new columns value is not.