TB16 182 – Fd. Medlem
Postad: 7 jun 2019 12:47

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? 

Svara
Close