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

Answer 1

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.