CODEWITHSUNDEEP
sqlinformix
FutureCap
FutureCap

Reputation: 55

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!

Upvotes: 1

Views: 848

Answers (1)

ughai
ughai

Reputation: 9890

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

Upvotes: 2

Related Questions