数据透视表问题，SQL Server

Question

查询：

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);

我的当前结果是：

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

现在我想将这些数据透视，以便天数成为列。 问题是我不知道如何实现这个结果 - 如何让第一行只表示它代表总和，第二行表示它计算了不同的ID数量。

非常感谢您提供任何建议和帮助。

到目前为止，我的尝试可能会使您的回答更容易，因为您不必编写所有强大的代码：

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;

我期望的结果（日期顺序不重要）：

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

- kolboc

---

尝试使用两个透视查询（第一个用于sum(TotalDue)，第二个用于UNION ALL中产品数量的计数），它应该能解决问题。 - B3S

Answer 1

您可以将两个数据透视表联合起来以实现此目的。

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 

- SqlKindaGuy

---

抱歉，我没有看到已经有答案了。如果大家想看另一种写法，我就留下来。 - SqlKindaGuy

Answer 2

您不能一次完成多个数据透视表，因此您需要进行两次操作：

;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;

请注意，我将PIVOT聚合函数从COUNT()更改为SUM()，因为您似乎正在添加产品ID。

我之所以进行了两个“PrePivots”，是因为PIVOT操作对所有未在PIVOT中引用的列进行了隐式的GROUP BY，因此其他列（例如第一个透视表的总计）将被分组，并且您将拥有每个不同总计值的行。

- EzLo