PIVOT SQL - Guidance

Question

I have the following code:

    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

This gives the follwing output:

Results

I would like to know the best way to apply Total columns to this

Desired output

Many thanks in advance for any feedback.

Hi, so a total column which which would sum up all products by colour (to the right of the 'uncolored column'), i.e. 32 Mountain Bikes in total. And also a toal column a the bottom which sums up all products by colour — abagg1, Dec 21 '17 at 15:00
@GordonLinoff I was thinking that maybe a rollup might satisfy the OP, but I am not sure how to do this here. — Tim Biegeleisen, Dec 21 '17 at 15:02
Please read http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557 and the accepted answer — , Dec 21 '17 at 15:08
You can find a response to a very similar question here: https://stackoverflow.com/questions/17140559/using-pivot-table-with-column-and-row-totals-in-sql-server-2008] — Ezin82, Dec 21 '17 at 17:24

Lukas Eder · Answer 1 · 2017-12-22T09:14:26.417

This is such a nice example of a CUBE() (or GROUPING SETS) calculation, combined with PIVOT representation, I had to write a blog post about it.

Here's the solution that produces exactly what you're looking for:

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

Ezin82 · Answer 2 · 2017-12-22T10:18:52.500

You could find a similar question here:

Using pivot table with column and row totals in sql server 2008

Using CUBE, a solution could be

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

Using CTE, an alternative solution could be

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

Your manual `UNION ALL` concatenation can be simplified using `CUBE` or `ROLLUP` / `GROUPING SETS` depending on the use-case. You're likely to get a better plan as I've shown [in this blog post](https://blog.jooq.org/2017/12/22/creating-a-microsoft-excel-style-pivot-table-with-grand-totals-in-sql/) — Lukas Eder, Dec 22 '17 at 09:16
Thanks for suggestion, I will try to post an alternative solution that use CUBO/ROLLUP as in the post https://stackoverflow.com/questions/17140559/using-pivot-table-with-column-and-row-totals-in-sql-server-2008] — Ezin82, Dec 22 '17 at 09:37

PIVOT SQL - Guidance

2 Answers2