How to pivot on multiple columns?

Question

I'm trying to pivot on multiple columns and I'm using SQL Server 2014, however, I cannot figure out how to do that. Here's what I've tried so far:

DECLARE @Table TABLE (
    Name NVARCHAR(MAX),
    TypeId INT,
    TotalOrders INT,
    GrandTotal MONEY
)

INSERT INTO @Table
    (Name, TypeId, TotalOrders, GrandTotal)
VALUES
    ('This Month', 1, 10, 1),
    ('This Month', 2, 5, 7),
    ('This Week', 1, 8, 3),
    ('Last Week', 1, 8, 12),
    ('Yesterday', 1, 10, 1),
    ('Yesterday', 2, 1, 5)

Which produces the following result:

Name                             TypeId      TotalOrders GrandTotal
-------------------------------- ----------- ----------- ---------------------
This Month                       1           10          1.00
This Month                       2           5           7.00
This Week                        1           8           3.00
Last Week                        1           8           12.00
Yesterday                        1           10          1.00
Yesterday                        2           1           5.00

To bring those rows into columns, I've tried this:

SELECT
    TypeId,
    ISNULL([Yesterday], 0) AS YesterdayTotalOrders,
    ISNULL([This Week], 0) AS ThisWeekTotalOrders,
    ISNULL([Last Week], 0) AS LastWeekTotalOrders,
    ISNULL([This Month], 0) AS ThisMonthTotalOrders
FROM
    (SELECT Name, TypeId, TotalOrders FROM @Table) AS src
PIVOT (
    SUM(TotalOrders) FOR Name IN (
        [Yesterday],
        [This Week],
        [Last Week],
        [This Month]
    )
) AS p1

Which produces the following result set:

TypeId      YesterdayTotalOrders ThisWeekTotalOrders LastWeekTotalOrders ThisMonthTotalOrders
----------- -------------------- ------------------- ------------------- --------------------
1           10                   8                   8                   10
2           1                    0                   0                   5

Now, I need to have few other columns for GrandTotal such as YesterdayGrandTotal, ThisWeekGrandTotal, and so on and so forth but I can't figure out how to achieve this.

Any help would be highly appreciated.

UPDATE#1: Here's the expected result set:

TypeId      YesterdayTotalOrders ThisWeekTotalOrders LastWeekTotalOrders ThisMonthTotalOrders YesterdayGrandTotal   ThisWeekGrandTotal    LastWeekGrandTotal    ThisMonthGrandTotal
----------- -------------------- ------------------- ------------------- -------------------- --------------------- --------------------- --------------------- ---------------------
1           10                   8                   8                   10                   1.00                  3.00                  12.00                 1.00
2           1                    0                   0                   5                    5.00                  0.00                  0.00                  7.00

Radim Bača · Accepted Answer · 2017-11-16 09:16:32Z

Conditional aggregation may be a solution:

select typeID,
    SUM(case when name = 'Yesterday' then totalOrders else 0 end) as YesterdayTotalOrders,
    SUM(case when name = 'This Week' then totalOrders else 0 end) as ThisWeekTotalOrders,
    SUM(case when name = 'Last Week' then totalOrders else 0 end) as LastWeekTotalOrders,
    SUM(case when name = 'This Month' then totalOrders else 0 end) as ThisMonthTotalOrders,
    SUM(case when name = 'Yesterday' then GrandTotal else 0 end) as YesterdayGrandTotal,
    SUM(case when name = 'This Week' then GrandTotal else 0 end) as ThisWeekGrandTotal,
    SUM(case when name = 'Last Week' then GrandTotal else 0 end) as LastWeekGrandTotal,
    SUM(case when name = 'This Month' then GrandTotal else 0 end) as ThisMonthGrandTotal    
from @table
group by typeID

or, you can use the CROSS APPLY and PIVOT like this

SELECT
    TypeId,
    ISNULL([Yesterday], 0) AS YesterdayTotalOrders,
    ISNULL([This Week], 0) AS ThisWeekTotalOrders,
    ISNULL([Last Week], 0) AS LastWeekTotalOrders,
    ISNULL([This Month], 0) AS ThisMonthTotalOrders,
    ISNULL([grant Yesterday], 0) AS YesterdayGrandTotal,
    ISNULL([grant This Week], 0) AS ThisWeekGrandTotal,
    ISNULL([grant Last Week], 0) AS LastWeekGrandTotal,
    ISNULL([grant This Month], 0) AS ThisMonthGrandTotal
FROM
    (
      SELECT t.* 
      FROM @Table
      CROSS APPLY (values(Name, TypeId, TotalOrders),
                     ('grant ' + Name, TypeId, GrandTotal)) 
                     t(Name, TypeId, TotalOrders)
    ) AS src
PIVOT (
    SUM(TotalOrders) FOR Name IN (
        [Yesterday],
        [This Week],
        [Last Week],
        [This Month],
        [grant Yesterday],
        [grant This Week],
        [grant Last Week],
        [grant This Month]
    )
) AS p1

demo

Both solutions will scan the input table just once and they have a very similar query plan. Both solutions are better than JOIN of two pivots (the solution that I have originally provided) since two pivots need to scan the input table twice.

Thank you. I liked the conditional aggregation solution, but let me see if someone else could come up with any better solution before marjing this as the answer. Thank you again.

Yogesh Sharma · Accepted Answer · 2017-11-16 08:00:02Z

You could also use of CTE by separating your pivots.. P1 for TotalOrders & P2 for GrandTotal

;WITH CTE AS 
   (
     SELECT
    P1.TypeId,
    ISNULL(P1.[Yesterday], 0) AS YesterdayTotalOrders,
    ISNULL(P1.[This Week], 0) AS ThisWeekTotalOrders,
    ISNULL(P1.[Last Week], 0) AS LastWeekTotalOrders,
    ISNULL(P1.[This Month], 0) AS ThisMonthTotalOrders
FROM
    (SELECT Name, TypeId, TotalOrders FROM @Table) AS src
PIVOT (SUM(TotalOrders) FOR src.Name IN (
        [Yesterday],
        [This Week],
        [Last Week],
        [This Month])) AS P1
   ), CTE1 AS 
   (

SELECT
    P1.TypeId,
    ISNULL(P1.[Yesterday], 0) AS YesterdayGrandTotal,
    ISNULL(P1.[This Week], 0) AS ThisWeekTGrandTotal,
    ISNULL(P1.[Last Week], 0) AS LastWeekGrandTotal,
    ISNULL(P1.[This Month], 0) AS ThisMonthGrandTotal
FROM
    (SELECT Name, TypeId, GrandTotal FROM @Table) AS src
PIVOT (SUM(GrandTotal) FOR src.Name IN (
        [Yesterday],
        [This Week],
        [Last Week],
        [This Month])) AS P1 
       )

       SELECT C.TypeId  ,    C.YesterdayTotalOrders, C.ThisWeekTotalOrders, C.LastWeekTotalOrders, C.ThisMonthTotalOrders , C1.YesterdayGrandTotal  , C1.ThisWeekTGrandTotal ,C1.LastWeekGrandTotal , C1.ThisMonthGrandTotal FROM CTE C 
       INNER JOIN CTE1 C1 ON C1.TypeId = C.TypeId

Result :

TypeId      YesterdayTotalOrders ThisWeekTotalOrders LastWeekTotalOrders ThisMonthTotalOrders YesterdayGrandTotal   ThisWeekGrandTotal    LastWeekGrandTotal    ThisMonthGrandTotal
----------- -------------------- ------------------- ------------------- -------------------- --------------------- --------------------- --------------------- ---------------------
1           10                   8                   8                   10                   1.00                  3.00                  12.00                 1.00
2           1                    0                   0                   5                    5.00                  0.00                  0.00                  7.00

Isn't there any better solution to the above problem? I mean is there any possibility to achieve the same thing without using joins? Because to my understanding, this means that the @Table records have to be fetched twice, once for each CTE (and calculating this in my real-world scenario isn't inexpensive). I was hoping to somehow obtain the same result set using the PIVOT only. Any advise?
@Mehdi multiple pivot options allowed but in your case you have same column which is invalid for multiple pivot.
What do you mean by having the "same column"? Could you please elaborate on this?

aljassi · Accepted Answer · 2017-11-16 09:11:05Z

IN oracle i would do something like tihs. it works fine for me

select * from (
SELECT   Name, TypeId,TotalOrders
--hier
,GrandTotal 
FROM test_b )
PIVOT (  SUM(TotalOrders)TotalOrders, 
--hier
SUM(grandtotal) grandtotal FOR Name IN 
('Yesterday'Yesterday,'This Week'ThisWeek,'Last Week'LastWeek,'This 
Month'ThisMonth )
) ;

so try this in sql server

SELECT *
   /* TypeId,
    ISNULL([Yesterday], 0) AS YesterdayTotalOrders,
    ISNULL([This Week], 0) AS ThisWeekTotalOrders,
   ISNULL([Last Week], 0) AS LastWeekTotalOrders,
   ISNULL([This Month], 0) AS ThisMonthTotalOrders*/
FROM
   (SELECT Name, TypeId, TotalOrders
   ,grandtotal 
  FROM @Table) AS src
PIVOT (
    SUM(TotalOrders)TotalOrders, SUM(grandtotal)grandtotal FOR Name IN (
    [Yesterday]Yesterday,
    [This Week]ThisWeek,
    [Last Week]LastWeek,
    [This Month]ThisMonth
   )
 ) AS p1

Collectives™ on Stack Overflow

How to pivot on multiple columns?

3 Answers 3

1 Comment

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Related