Kata SQL

SQL Basics: Simple PIVOTING data WITHOUT CROSSTAB

This kata is inspired by SQL Basics: Simple PIVOTING data by matt c.

You need to build a pivot table WITHOUT using CROSSTAB function. Having two tables products and details you need to select a pivot table of products with counts of details occurrences (possible details values are ['good', 'ok', 'bad'].

Results should be ordered by product’s name.

Model schema for the kata is:

your query should return table with next columns

  • name
  • good
  • ok
  • bad

Compare your table to the expected table to view the expected results.

Solution:

SELECT 
p.name,
COUNT((CASE when detail = 'good' then detail end)) AS good,
COUNT((CASE when detail = 'ok' then detail end)) AS ok,
COUNT((CASE when detail = 'bad' then detail end)) AS bad
FROM details d, products p
WHERE d.product_id = p.id
GROUP BY name
ORDER BY name;

Link

Reference

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store