To select multiple selects with different wheres I used:
SELECT table1.IDvar,
(CASE WHEN table2.var1 = 'foo' THEN table2.var2 END) AS condition1,
(CASE WHEN table2.var1 = 'bar' THEN table2.var2 END) AS condition2
FROM table1
FULL JOIN table2 ON table1.IDvar = table2.table1_IDvar
Unfortunately, this returns multiple rows per ID rather than containing each select column alias in different columns in the same row:
| IDvar | condition1 | condition2 |
|---|---|---|
| x1 | 2 | (null) |
| x1 | (null) | (null) |
| x1 | (null) | 33 |
| h2 | 1000 | (null) |
| h2 | (null) | (null) |
| h2 | (null) | (null) |
| z3 | (null) | 0 |
| z3 | 300 | (null) |
| z3 | (null) | (null) |
Note: each ID value does not appear with an equal number of rows.
How do I return results of different (CASE WHEN END) AS x (or similar filtering concept) expressions in the same row per unique ID? An ouput something like this:
| IDvar | condition1 | condition2 |
|---|---|---|
| x1 | 2 | 33 |
| h2 | 1000 | (null) |
| z3 | 300 | 0 |
CodePudding user response：
The operation you're attempting to do is called "pivot" and follows two steps:
- conditional selection through a
CASEexpression for each kind of field you want to extract - aggregation on a field for which we have separated information
Your query is fine, it's just missing the second step.
SELECT table1.IDvar,
MAX(CASE WHEN table2.var1 = 'foo' THEN table2.var2 END) AS condition1,
MAX(CASE WHEN table2.var1 = 'bar' THEN table2.var2 END) AS condition2
FROM table1
FULL JOIN table2 ON table1.IDvar = table2.table1_IDvar
GROUP BY table1.IDvar