Adding new data in columns, from another table – SQL ORACLE

I have a table with some records

MASTER TABLE

x------x--------------------x-------x
| Id   |      PERIOD        |   QTY |
x------x--------------------x-------x
|  1   |   2014-01-13       |   10  |
|  2   |   2014-01-06       |   30  |
x------x--------------------x-------x

I have another table with parameters of this record (ID)

TABLE2

x------x--------------------x------------x
| Id   |      Parameter        |   Value |
x------x--------------------x------------x
|  1   |   Humidty             |   10    |
|  1   |   Temperature         |   30    |
|  2   |   Humidty             |   50    |
|  2   |   Temperature         |   40    |
x------x--------------------x------------x

As result I want this: (combine based on ID)

Result table

x------x--------------------x-------------------------x
| Id   |      Period        |   Humidty | Temperature |
x------x--------------------x-------------------------x
|  1   |   2014-01-13       |   10      | 30          |
|  2   |   2014-01-06       |   50      | 40          |
x------x--------------------x-------------------------x

How Can I do something like that? Inner join will not work I think.

Answer

Join the tables and use conditional aggregation with case to extract the 2 columns:

select t1.id, t1.period,
  max(case when t2.parameter = 'Humidty' then t2.value end) Humidty,
  max(case when t2.parameter = 'Temperature' then t2.value end) Temperature
from mastertable t1 inner join table2 t2
on t2.id = t1.id
group by t1.id, t1.period

Advertisement

Answer