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

这将给出以下输出：

​

​

我想知道将Total列应用于此的最佳方法

期望输出

​

非常感谢您的反馈。

Answer 1

这是一个很好的结合了PIVOT representation、I had to write a blog post about it的CUBE() (or GROUPING SETS) calculation的例子。

这里有一个解决方案，它能产生你想要的东西：

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

Answer 2

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

Using pivot table with column and row totals in 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