LordGhettofaust LordGhettofaust - 5 months ago 8
SQL Question

SQL Select - transform rows in columns

Quite basic, but I am stuck at the moment.

On an Informix database (no pivot option), I am searching for a dynamic way to transform the following table using SQL:


book | info | value
-----------------------------
Moby Dick | price | high
Moby Dick | stock | few
Hamlet | price | low
Hamlet | stock | many
Faust | price | medium
Faust | stock | normal


Resulting table:


book | price | stock
-----------------------------
Moby Dick | high | few
Hamlet | low | many
Faust | medium | normal


Thanks for your help!

Answer

You can aggregate based on CASE expression grouped by book. Try something like this.

SELECT book,
MAX(CASE WHEN info = 'price' THEN value END) as price,
MAX(CASE WHEN info = 'stock' THEN value END) as stock
FROM table1
GROUP BY book