Next step from here:
On an Informix database (no pivot option), I am searching for a dynamic way to transform the following tables using SQL:
First table: "catalog"
book | info | value ----------------------------- Moby Dick | price | high Moby Dick | stock | few Hamlet | price | low Hamlet | stock | many Faust | price | medium Faust | stock | normal
Second table: "artists"
book | author ------------------------------------------ Moby Dick | Herman Melville Hamlet | William Shakespeare Faust | Johann Wolfgang von Goethe
The resulting table, which I want to achieve:
book | price | stock | author ------------------------------------------------------------- Moby Dick | high | few | Herman Melville Hamlet | low | many | William Shakespeare Faust | medium | normal | Johann Wolfgang von Goethe
The code I have in mind so far to reach the resulting table looks as follows.
Combining two tables:
SELECT T1.book,
T1.info,
T1.value,
T2.book,
T2.author
FROM catalog T1,
artists T2
WHERE T1.book=T2.book
Transforming rows to columns:
SELECT book, MAX(CASE WHEN info = 'price' THEN value END) as price, MAX(CASE WHEN info = 'stock' THEN value END) as stock FROM catalog GROUP BY book
But unfortunately, I am unable to combine those two.
Thanks for your help!
Is this what you want?
SELECT T1.book,
MAX(CASE WHEN info = 'price' THEN value END) as price,
MAX(CASE WHEN info = 'stock' THEN value END) as stock,
MAX(T2.author) as author
FROM catalog T1
JOIN artists T2
ON T1.book = T2.book
GROUP BY T1.book;
OUTPUT
| book | price | stock | author |
|-----------|--------|--------|----------------------------|
| Faust | medium | normal | Johann Wolfgang von Goethe |
| Hamlet | low | many | William Shakespeare |
| Moby Dick | high | few | Herman Melville |