SQL pivot tables without the CROSSTAB function, just for fun

pivot_with_case.sql

SELECT c.name,
  MAX(CASE detail WHEN 'good' THEN quantity ELSE 0 END) AS good,
  MAX(CASE detail WHEN 'ok' THEN quantity ELSE 0 END) AS ok,
  MAX(CASE detail WHEN 'bad' THEN quantity ELSE 0 END) AS bad 
  FROM
 (
   SELECT name, detail, count(*) as quantity
   FROM products p
   JOIN details d ON p.id = d.product_id
   GROUP BY name, detail
   ORDER BY name, detail
 ) c
GROUP BY c.name

pivot_with_cte.sql

WITH pd AS (
  SELECT name, COUNT(*) quantity, detail
  FROM products
  JOIN details ON products.id = details.product_id
  GROUP BY name, detail
)
SELECT DISTINCT n.name,
 g.quantity good,
 ok.quantity ok,
 b.quantity bad
FROM pd n
JOIN pd g ON n.name = g.name AND g.detail = 'good'
JOIN pd ok ON n.name = ok.name AND ok.detail = 'ok'
JOIN pd b ON n.name = b.name AND b.detail = 'bad'