PostgreSQL - Simple PIVOTING data
Uppgift:
"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."
Källa:https://www.codewars.com/kata/sql-basics-simple-pivoting-data/train/sql
Föväntat slutresultat:
Försök 1 (med CASE):
CREATE EXTENSION tablefunc;
SELECT products.name, details.detail,
SUM(CASE details.detail WHEN 'bad' THEN count ELSE 0 END) AS bad,
SUM(CASE details.detail WHEN 'good' THEN count ELSE 0 END) AS good,
SUM(CASE details.detail WHEN 'ok' THEN count ELSE 0 END) AS ok
FROM products, details
GROUP BY products.name
ORDER BY products.name
Felmeddelande: PG::UndefinedColumn: ERROR: column "count" does not exist
LINE 3: SUM(CASE details.detail WHEN 'bad' THEN count ELSE 0 END) AS...
Försök 2(utan CASE):
CREATE EXTENSION tablefunc;
SELECT * FROM crosstab(
'SELECT products.name, details.detail, ct
FROM products, details
WHERE products.id = details.id
GROUP BY products.name
ORDER BY 1,3'
) AS ct ("name" text, "bad" text, "good" text, "ok" text);
Felmeddelande:
PG::UndefinedColumn: ERROR: column "ct" does not exist
LINE 1: SELECT products.name, details.detail, ct
Fråga:
Är det någon som ser vad jag gör för fel?