PIVOT SQL - 指南

Question

我有以下代码：

    SELECT * 
    FROM
        (   
        SELECT p.ProductID, pc.Name, ISNULL(p.Color, 'Uncolored') AS Color
        FROM SalesLT.ProductCategory AS pc
        INNER JOIN SalesLT.Product AS p ON pc.ProductCategoryID = p.ProductCategoryID
        )
    AS PPC
    PIVOT (COUNT(ProductID) FOR COLOR IN ([Red], [Blue], [Black], [Silver], [Yellow], [Grey], [Multi], [Uncolored]))
    AS ColorPivotTable

这将产生以下输出：

我想知道如何最好地应用总列到这个表格中。

期望的输出 非常感谢您提前的任何反馈。

- abagg1

---

请解释一下 "apply total columns" 的含义。 - Gordon Linoff

嗨，需要一个总列来按颜色汇总所有产品（在“未着色列”的右侧），即总共32辆山地自行车。还需要一个底部的总列，按颜色汇总所有产品。 - abagg1

@GordonLinoff 我在想也许一个 rollup 可能会满足 OP 的需求，但我不确定如何在这里实现。 - Tim Biegeleisen

你可以在一个CTE中获取总数，然后左连接它。 - dbajtr

请阅读http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557以及被接受的答案。 - user330315

你可以在这里找到一个非常类似的问题的答案：https://dev59.com/EmQn5IYBdhLWcg3wCzik - Ezin82

Answer 1

这是一个非常好的示例，结合了CUBE()（或GROUPING SETS）计算，与PIVOT表示法相结合，我不得不写一篇博客文章来介绍它。

以下是能够生成您想要的结果的解决方案：

WITH Bikes(Name, Colour) AS (
  SELECT * FROM (
    VALUES ('Mountain Bikes', 'Black'), 
           ('Mountain Bikes', 'Black'),
           ('Mountain Bikes', 'Silver'),
           ('Road Bikes', 'Red'),
           ('Road Bikes', 'Red'),
           ('Road Bikes', 'Red'),
           ('Road Bikes', 'Black'),
           ('Road Bikes', 'Yellow') 
  ) AS Bikes(Name, Colour)
)
SELECT
  Name, 
  COALESCE(Red, 0) AS Red, 
  COALESCE(Blue, 0) AS Blue, 
  COALESCE(Black, 0) AS Black, 
  COALESCE(Silver, 0) AS Silver, 
  COALESCE(Yellow, 0) AS Yellow, 
  COALESCE(Grey, 0) AS Grey, 
  COALESCE(Multi, 0) AS Multi, 
  COALESCE(Uncoloured, 0) AS Uncoloured, 
  Total
FROM (
  SELECT 
    Coalesce(Name, 'Total') Name, 
    COALESCE(Colour, 'Total') Colour, 
    COUNT(*) Count
  FROM Bikes
  GROUP BY CUBE (Name, Colour)
) AS t
PIVOT (
  MAX(Count) FOR Colour IN (
    Red, Blue, Black, Silver, Yellow, Grey, Multi, Uncoloured, Total
  )
) AS p
ORDER BY CASE Name WHEN 'Total' THEN 1 ELSE 0 END, Name

SQLFiddle here

- Lukas Eder

Answer 2

你可以在这里找到类似问题：

在 SQL Server 2008 中使用带有列和行总计的数据透视表

使用 CUBE，一个解决方案可能是

WITH SalesLT_ProductCategory as (
    SELECT * FROM (
        VALUES 
            (1, 'Mountain Bikes'),
            (2, 'Road Bikes'),
            (3, 'Touring Bikes'),
            (4, 'Brakes')
            -- etc...
        ) AS a (ProductCategoryID, Name)
), SalesLT_Product AS (
    SELECT * FROM (
        VALUES 
            (1, 1, 'Red'),
            (1, 1, 'Blue'),
            (1, 1, 'Blue'),
            (1, 1, 'Blue'),

            (1, 2, 'Red'),
            (1, 2, 'Red'),
            (1, 2, 'Blue'),
            (1, 2, 'Black'),

            (1, 3, 'Black'),
            (1, 3, 'Yellow'),
            (1, 3, 'Grey'),
            (1, 3, 'Grey'),
            (1, 3, 'Grey'),

            (1, 4, 'Red'),
            (1, 4, 'Multi'),
            (1, 4, 'Multi'),
            (1, 4, 'Uncolored'),
            (1, 4, 'Uncolored'),
            (1, 4, 'Uncolored')
            -- etc...
        ) AS a (ProductID, ProductCategoryID, Color)
), BaseData AS (
        SELECT p.ProductID, pc.Name, ISNULL(p.Color, 'Uncolored') AS Color
        FROM SalesLT_ProductCategory AS pc
        INNER JOIN SalesLT_Product AS p ON pc.ProductCategoryID = p.ProductCategoryID
)
SELECT
  Name, 
  COALESCE(Red, 0) AS Red, 
  COALESCE(Blue, 0) AS Blue, 
  COALESCE(Black, 0) AS Black, 
  COALESCE(Silver, 0) AS Silver, 
  COALESCE(Yellow, 0) AS Yellow, 
  COALESCE(Grey, 0) AS Grey, 
  COALESCE(Multi, 0) AS Multi, 
  COALESCE(Uncoloured, 0) AS Uncoloured, 
  Total
FROM (
  SELECT
    COALESCE(Name, 'Total') AS Name, 
    COALESCE(Color, 'Total') AS Color, 
    COUNT(*) AS Count
  FROM BaseData
  GROUP BY CUBE (Name, Color)
) AS t
PIVOT (
  SUM(Count) FOR Color IN (
    Red, Blue, Black, Silver, Yellow, 
    Grey, Multi, Uncoloured, Total
  )
) AS p
ORDER BY CASE Name WHEN 'Total' THEN 1 ELSE 0 END, Name

使用CTE，另一种解决方案可能是

WITH SalesLT_ProductCategory as (
    SELECT * FROM (
        VALUES 
            (1, 'Mountain Bikes'),
            (2, 'Road Bikes'),
            (3, 'Touring Bikes'),
            (4, 'Brakes')
            -- etc...
        ) AS a (ProductCategoryID, Name)
), SalesLT_Product as (
    SELECT * FROM (
        VALUES 
            (1, 1, 'Red'),
            (1, 1, 'Blue'),
            (1, 1, 'Blue'),
            (1, 1, 'Blue'),

            (1, 2, 'Red'),
            (1, 2, 'Red'),
            (1, 2, 'Blue'),
            (1, 2, 'Black'),

            (1, 3, 'Black'),
            (1, 3, 'Yellow'),
            (1, 3, 'Grey'),
            (1, 3, 'Grey'),
            (1, 3, 'Grey'),

            (1, 4, 'Red'),
            (1, 4, 'Multi'),
            (1, 4, 'Multi'),
            (1, 4, 'Uncolored'),
            (1, 4, 'Uncolored'),
            (1, 4, 'Uncolored')
            -- etc...
        ) AS a (ProductID, ProductCategoryID, Color)
), PivotData AS (
    -- your query
    SELECT * 
    FROM
        (   
        SELECT p.ProductID, pc.Name, ISNULL(p.Color, 'Uncolored') AS Color
        FROM SalesLT_ProductCategory AS pc
        INNER JOIN SalesLT_Product AS p ON pc.ProductCategoryID = p.ProductCategoryID
        )
    AS PPC
    PIVOT (COUNT(ProductID) FOR COLOR IN ([Red], [Blue], [Black], [Silver], [Yellow], [Grey], [Multi], [Uncolored]))
    AS ColorPivotTable
), ColumnTotals AS (
    -- column totals
    SELECT
          'Total' AS Name
        , SUM(Red) AS Red
        , SUM(Blue) AS Blue
        , SUM(Black) AS Black
        , SUM(Silver) AS Silver
        , SUM(Yellow) AS Yellow
        , SUM(Grey) AS Grey
        , SUM(Multi) AS Multi
        , SUM(Uncolored )AS Uncolored
    FROM PivotData
), PivotDataWithRowTotals AS (
SELECT * FROM PivotData
UNION ALL 
SELECT * FROM ColumnTotals
)
SELECT P.*
    -- row totals
    , P.Red + P.Blue + P.Black + P.Silver + P.Yellow + P.Grey + P.Multi + P.Uncolored as Total
FROM PivotDataWithRowTotals AS P

- Ezin82

---

您可以根据使用情况，使用CUBE或ROLLUP/GROUPING SETS来简化手动的UNION ALL连接。正如我在这篇博客文章中所展示的那样，您很可能会得到更好的计划。 - Lukas Eder

感谢您的建议，我将尝试发布一种替代解决方案，该解决方案使用CUBO/ROLLUP，如帖子https://dev59.com/EmQn5IYBdhLWcg3wCzik所述。 - Ezin82