I have a table with some records
MASTER TABLE
x
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.
Advertisement
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