Pivot trouble, SQL Server

Question

Query:

SELECT   
SUM(TotalDue), DATENAME(dw, OrderDate), COUNT(DISTINCT ProductID)  
FROM Sales.SalesOrderHeader headers  
INNER JOIN Sales.SalesOrderDetail details  
ON  headers.SalesOrderID = details.SalesOrderID  
GROUP BY DATENAME(dw, OrderDate);

my current result is:

560644420,8959  Wednesday   262
432484099,6362  Thursday    260
478917953,3263  Saturday    251
212585108,7496  Friday  238
457072471,4514  Monday  241
381773345,5659  Sunday  259
403492724,4161  Tuesday 235

now i would like to pivot that data so I have days as columns. Problem is i don't know how to achieve that result - how to make first row just tell that it represents sums, and second that it counts different ids.

Thanks in advance for any suggestions and help

my tries so far, maybe that query would make your answering easier as you wouldn't have to write all the robust code:

SELECT [Total], [Monday], [Tuesday]
FROM 
    (
        SELECT SUM(TotalDue)[Total], DATENAME(dw, OrderDate) [Day], COUNT(DISTINCT ProductID)[different products]
        FROM Sales.SalesOrderHeader headers
        INNER JOIN Sales.SalesOrderDetail details ON  headers.SalesOrderID = details.SalesOrderID
        GROUP BY DATENAME(dw, OrderDate)
) as source
PIVOT(
    COUNT([different products]) FOR [Day] IN ([Monday], [Tuesday])
) as pivoted;

My expected result ( day order is not important) :

                        Monday            Tuesday         Wednesday ...
Sums of orders          457072471,4514    403492724,4161  560644420,8959
Different items count   241               235             262

Answer 1

You can just union two pivot statements to do this..

Select  Totals,Monday  ,Tuesday from (
SELECT  'TotalSum' As Totals, [Monday] as Monday, [Tuesday]
FROM 
    (
        SELECT sum(Totaldue) As TotalDue, DATENAME(dw, OrderDate) [Day] 
        FROM Sales.SalesOrderHeader headers
        INNER JOIN Sales.SalesOrderDetail details ON  headers.SalesOrderID = details.SalesOrderID
        group by  DATENAME(dw, OrderDate) 


) as source
PIVOT(
    sum(TotalDue) FOR [Day] IN ([Monday], [Tuesday])
    ) as pivoted

    union all

SELECT  'TotalCount' As Totals, [Monday] as Monday, [Tuesday]
FROM 
    (
        SELECT  DATENAME(dw, OrderDate) [Day], count(distinct ProductID) as DistinctProduct
        FROM Sales.SalesOrderHeader headers
        INNER JOIN Sales.SalesOrderDetail details ON  headers.SalesOrderID = details.SalesOrderID
        group by  DATENAME(dw, OrderDate) 


) as source
PIVOT(
    sum(DistinctProduct) FOR [Day] IN ([Monday], [Tuesday]) 
    )as pivoted
    )  x 

Answer 2

You can't do multiple pivots in one go, so you have to do it twice:

;WITH Data AS
(
    SELECT SUM(TotalDue)[Total], DATENAME(dw, OrderDate) [Day], COUNT(DISTINCT ProductID)[different products]
    FROM 
        Sales.SalesOrderHeader headers
        INNER JOIN Sales.SalesOrderDetail details ON  headers.SalesOrderID = details.SalesOrderID
    GROUP BY 
        DATENAME(dw, OrderDate)
),
PrePivot1 AS
(
    SELECT
        D.Day,
        D.[different products]
    FROM
        Data AS D
),
PrePivot2 AS
(
    SELECT
        D.Day,
        D.[Total]
    FROM
        Data AS D
)
SELECT
    Concept = 'Different items count',
    pivoted.Monday,
    pivoted.Tuesday,
    pivoted.Wednesday,
    pivoted.Thursday,
    pivoted.Friday,
    pivoted.Saturday,
    pivoted.Sunday
FROM
    PrePivot1 AS D
    PIVOT (
        SUM([different products]) FOR [Day] IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])
    ) as pivoted

UNION ALL

SELECT
    Concept = 'Sums of orders',
    pivoted.Monday,
    pivoted.Tuesday,
    pivoted.Wednesday,
    pivoted.Thursday,
    pivoted.Friday,
    pivoted.Saturday,
    pivoted.Sunday
FROM
    PrePivot2 AS D
    PIVOT (
        SUM([Total]) FOR [Day] IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])
    ) as pivoted;

Note that I changed the PIVOT aggregate function from COUNT() to SUM(), since it seems you are adding product IDs.

The reason I'm doing 2 "PrePivots" is because the PIVOT operation does an implicit GROUP BY by all the columns not referenced in the PIVOT, so other columns (like Total for the first pivot) would be grouped by and you would have a row for each diferent Total value.