Understanding SQL Server PIVOT and UNPIVOT with example

PIVOT and UNPIVOT are relational operators for converting table expressions into one another. If we want to turn the unique row values of a column into multiple columns we use the PIVOT operator. And when we want to converge multiple columns to column rows of records we use UNPIVOT operator. This transformation and aggregation of the dataset are also known as transpose. Using the PIVOT operator we can transpose rows to columns and perform aggregations during the process, and with UNPIVOT operator we transpose multiple columns to a single column with multiple rows of records. This transposing of the dataset from rows to columns and from columns to a column with rows of records is useful many times, mostly in cases of generating comparison reports, etc. For this post, we will also be using a similar dataset, maintaining order_date and total order_amount for the date in the orders_demo table, for a better understanding of the PIVOT and UNPIVOT operators.

drop table if exists orders_demo;
create table orders_demo(
    order_date datetime2 not null,
    order_amount numeric(10,2) not null default(0.0)
);

insert into orders_demo (order_date, order_amount) values ('2021-08-29 06:24:36', 96645.23);
insert into orders_demo (order_date, order_amount) values ('2021-08-17 12:57:42', 2799.66);
insert into orders_demo (order_date, order_amount) values ('2021-12-08 01:21:10', 36855.52);
insert into orders_demo (order_date, order_amount) values ('2021-09-04 02:58:29', 12745.25);
insert into orders_demo (order_date, order_amount) values ('2021-06-02 05:52:27', 19305.53);
insert into orders_demo (order_date, order_amount) values ('2021-08-01 21:35:38', 44003.67);
insert into orders_demo (order_date, order_amount) values ('2021-06-29 07:31:51', 9620.3);
insert into orders_demo (order_date, order_amount) values ('2021-05-16 13:41:58', 83331.23);
insert into orders_demo (order_date, order_amount) values ('2021-05-14 10:34:52', 52047.06);
insert into orders_demo (order_date, order_amount) values ('2021-10-28 10:52:08', 87767.65);
insert into orders_demo (order_date, order_amount) values ('2021-09-07 15:30:38', 79037.05);
insert into orders_demo (order_date, order_amount) values ('2021-06-26 17:09:48', 45204.9);
insert into orders_demo (order_date, order_amount) values ('2021-05-23 17:32:55', 1529.24);
insert into orders_demo (order_date, order_amount) values ('2021-08-07 15:40:40', 40596.98);
insert into orders_demo (order_date, order_amount) values ('2021-07-18 07:45:15', 72129.59);
insert into orders_demo (order_date, order_amount) values ('2021-10-29 16:29:39', 5195.02);
insert into orders_demo (order_date, order_amount) values ('2021-10-14 10:21:15', 21266.39);
insert into orders_demo (order_date, order_amount) values ('2021-09-13 06:21:35', 43578.89);
insert into orders_demo (order_date, order_amount) values ('2021-06-25 04:57:38', 5705.68);
insert into orders_demo (order_date, order_amount) values ('2021-10-04 17:41:11', 48334.64);
insert into orders_demo (order_date, order_amount) values ('2021-09-24 08:54:28', 90635.05);
insert into orders_demo (order_date, order_amount) values ('2021-11-01 07:47:06', 9952.95);
insert into orders_demo (order_date, order_amount) values ('2021-05-17 09:05:54', 82753.71);
insert into orders_demo (order_date, order_amount) values ('2021-06-21 21:11:44', 44711.68);
insert into orders_demo (order_date, order_amount) values ('2021-06-20 09:02:21', 95524.26);
insert into orders_demo (order_date, order_amount) values ('2021-08-11 17:24:09', 51805.88);
insert into orders_demo (order_date, order_amount) values ('2021-07-08 02:23:24', 56710.13);
insert into orders_demo (order_date, order_amount) values ('2021-12-14 14:15:48', 39827.63);
insert into orders_demo (order_date, order_amount) values ('2021-09-08 09:04:30', 70854.91);
insert into orders_demo (order_date, order_amount) values ('2021-07-23 01:42:31', 36303.79);

-- select * from orders_demo

PIVOT

Looking at the dataset we can see that it has a date range of a few months. Now suppose we want to arrange and transpose the dataset in such a way that, we want to show average order amounts for each month in the data set as month-wise columns.

select 
    'average_order_amount' as avg_order_amounts,
    [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]
from 
(
    select month(order_date) as month_num, order_amount
        from 
    orders_demo
) as s
pivot 
(
    avg(order_amount)
    for month_num in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) as avg_pivot_month

The above SQL snippet will return a row with columns for all 12 months and the average order total as its value. The first 4 months will be having NULL value as we don't have a dataset for those date ranges.

UNPIVOT

As mentioned earlier in the introduction part, UNPIVOT operator transposes multiple columns to a single column with multiple rows of records and returns as a dataset. In the earlier part, we used the PIVOT operator on the orders_demo table and returned a row containing a name column and 12 other columns for each month of the year. Now suppose, if we want the result set such that, it will have only two columns, one named month_num depicting the month number and the other average_order_amount depicting the average of all the order amount for the month from the orders_demo table.

To transpose the dataset in such a way, we will use the above pivot query as a source and return the values of the multiple columns of the source result set in a single column as the average order amount for the month.

select
    month_num,
    average_amounts
from
(
    select 
        'average_order_amount' as avg_order_amounts,
        [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]
    from 
    (
        select month(order_date) as month_num, order_amount
            from 
        orders_demo
    ) as s
    pivot 
    (
        avg(order_amount)
        for month_num in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
    ) as avg_pivot_month
) source_set 
unpivot
(
    average_amounts for month_num in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) as avg_unpivot;

In the above SQL snippet, we first used the PIVOT operator to return a row containing a name for the average_name column along with 12 other columns containing the values of the average order amount for each month, the result set is named avg_pivot_month. Later using that result set with UNPIVOT operator we transposed those 12 multiple columns of the month to 1 column named month_num and its values in the average_amounts column. And remember that since there is no record for the first 4 months in our table set, we will not get those months in the month_num column. Note that UNPIVOT operator doesn't split up the aggregated results, hence UNPIVOT operator is not exactly the opposite of the PIVOT operator.

In the next post regarding PIVOT and UNPIVOT, we will be trying to implement a dynamic SQL PIVOT and UNPIVOT script/function with an example dataset.