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:

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

Desired output

Many thanks in advance for any feedback.

Answer 1

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