LordGhettofaust LordGhettofaust - 4 months ago 8
SQL Question

SQL Select - combine two tables (while transforming rows in columns)

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 | low | Herman Melville
Hamlet | low | high | 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!

Answer

Is this what you want?

SQL Fiddle Demo

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 |