CODEWITHSUNDEEP
sqlsql-serverpivotpivot-table
Lokapedia
Lokapedia

Reputation: 105

how to create calculated pivot in sql

Scenario

Company XYZ sells products. Company measures their performance by checking how long does it take to complete the entire order. Each order goes through multiple status (Example : Open, Pending, Close)

They count days for each status to know how many days an order was on a particular status. the days are calculated in two different ways, Working Days and Calendar Days

Please refer to the below table:

enter image description here

Question

How to turn this table into a pivot table shown in below picture? also how to add to additional columns which is total of each Working and business day status.

Desired Result:

enter image description here

Upvotes: 1

Views: 74

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

You can do this:

SELECT *
FROM
(
    SELECT 
        OrderID,
        OrderStatus + CountType AS StatusType,
        DayCount
    FROM CalendarTable     
    UNION ALL
    SELECT 
    OrderID,
    CASE WHEN CountType = 'Working' THEN 'TotalWorking' ELSE 'TotalCalendar' END,
    DayCount
    FROM CalendarTable
) AS t
PIVOT
(
   MAX(DayCount)
   For StatusType IN(OpenWorking,
                     OpenCalendar,
                     CloseWorking,
                     CloseCalendar,
                     PendingWorking,
                     PendingCalendar,
                     TotalWorking,
                     TotalCalendar)
) AS p;

This will give you:

enter image description here

If you don't want to write down all the statuses manually, then you can do ti dynamically:

DECLARE @cols AS NVARCHAR(MAX);

DECLARE @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(StatusType)
                       FROM 
                       (
                            SELECT 
                              OrderID,
                              OrderStatus + CountType AS StatusType,
                              DayCount
                            FROM CalendarTable     
                            UNION ALL
                            SELECT 
                              OrderID,
                              CASE WHEN CountType = 'Working' THEN 'TotalWorking' ELSE 'TotalCalendar' END,
                              DayCount
                            FROM CalendarTable
                        ) AS t
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');


SELECT @query = 'SELECT *
                FROM
                (
                    SELECT 
                      OrderID,
                      OrderStatus + CountType AS StatusType,
                      DayCount
                    FROM CalendarTable     
                    UNION ALL
                    SELECT 
                      OrderID,
                      CASE WHEN CountType = ''Working'' THEN ''TotalWorking'' ELSE ''TotalCalendar'' END,
                      DayCount
                    FROM CalendarTable
                ) AS t
                PIVOT
                (
                   MAX(DayCount)
                   For StatusType IN(' + @cols + ')' +
                  ') p';

execute(@query);

Update:

For column names you can create a new variable @colnames and populate it with the names you want. For the Totals, you can add a WHERE clause to get the total for statuses active and pending only. So your query will be like this:

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @colnames AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(StatusType)
                       FROM 
                       (
                            SELECT 
                              OrderID,
                              OrderStatus + CountType AS StatusType,
                              DayCount
                            FROM CalendarTable     
                            UNION ALL
                            SELECT 
                              OrderID,
                              CASE WHEN CountType = 'Working' THEN 'TotalWorking' ELSE 'TotalCalendar' END,
                              DayCount
                            FROM CalendarTable
                            WHERE OrderStatus IN('Active', 'Pending')
                        ) AS t
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');


SELECT @colnames = STUFF((SELECT distinct ',' +
                        QUOTENAME(StatusType) + ' AS ' + QUOTENAME(StatusTypeName)
                       FROM 
                       (
                           SELECT 
                                OrderID,
                                OrderStatus + CountType AS StatusType,
                                DayCount,
                                OrderStatus + CASE WHEN CountType = 'Working' THEN  'WorkDays' ELSE 'CalDays' END AS StatusTypeName
                            FROM CalendarTable     
                            UNION ALL
                            SELECT 
                              OrderID,
                              CASE WHEN CountType = 'Working' THEN 'TotalWorking' ELSE 'TotalCalendar' END,
                              DayCount,
                              CASE WHEN CountType = 'Working' THEN 'TotalWorking' ELSE 'TotalCalendar' END
                            FROM CalendarTable
                            WHERE OrderStatus IN('Active', 'Pending')
                        ) AS t
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

SELECT @query = 'SELECT OrderID , ' + @colnames + '
                FROM
                (
                    SELECT 
                      OrderID,
                      OrderStatus + CountType AS StatusType,
                      DayCount
                    FROM CalendarTable     
                    UNION ALL
                    SELECT 
                      OrderID,
                      CASE WHEN CountType = ''Working'' THEN ''TotalWorking'' ELSE ''TotalCalendar'' END,
                      DayCount
                    FROM CalendarTable
                    WHERE OrderStatus IN(''Active'', ''Pending'')
                ) AS t
                PIVOT
                (
                   SUM(DayCount)
                   For StatusType IN(' + @cols + ')' +
                  ') p';

execute(@query);

This will give you:

enter image description here

Update

If you want to add a where clause to the manual pivot query, you can do this:

SELECT *
FROM
(
    SELECT 
        OrderID,
        OrderStatus + CountType AS StatusType,
        DayCount
    FROM CalendarTable     
    WHERE ...
    UNION ALL
    SELECT 
    OrderID,
    CASE WHEN CountType = 'Working' THEN 'TotalWorking' ELSE 'TotalCalendar' END,
    DayCount
    FROM CalendarTable
    WHERE ...
) AS t
PIVOT
(
   MAX(DayCount)
   For StatusType IN(OpenWorking,
                     OpenCalendar,
                     CloseWorking,
                     CloseCalendar,
                     PendingWorking,
                     PendingCalendar,
                     TotalWorking,
                     TotalCalendar)
) AS p;

Upvotes: 1

Related Questions