LordGhettofaust LordGhettofaust - 1 year ago 99
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!


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