Codewars SQL

SQL Basics: Simple PIVOTING data

For this challenge you need to PIVOT data. You have two tables, products and details. Your task is to pivot the rows in products to produce a table of products which have rows of their detail. Group and Order by the name of the Product.

Tables and relationship below:

You must use the CROSSTAB statement to create a table that has the schema as below:

CROSSTAB table.

  • name
  • good
  • ok
  • bad

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

PostgreSQL Documentation

Solution without crosstab:

select name, g.count as good, o.count as ok, b.count as bad
from products p
join (select product_id, count(*) as count from details where detail = 'good' group by product_id)g on (p.id = g.product_id)
join (select product_id, count(*) as count from details where detail =
'ok' group by product_id)o on (p.id = o.product_id)
join (select product_id, count(*) as count from details where detail =
'bad' group by product_id) b on (p.id = b.product_id)
group by name, g.count, o.count, b.count
order by name

Solution with crosstab:

CREATE EXTENSION tablefunc;

SELECT *
FROM crosstab(
'SELECT p.name, detail, COUNT(d.id)
FROM products p
JOIN details d
ON p.id = d.product_id
GROUP BY p.name, d.detail
ORDER BY 1,2')
AS ct (name text, bad bigint, good bigint, ok bigint)

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
Isabelle

Isabelle

In love with telling stories with data