1

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
Johan
  • 186
  • 15
  • 2
    Your question as it currently stands doesn't make sense. Your query has an incorrect syntax and even when fixing this, it's unclear what you want to do. Please show some sample input and the expected result for this data rather than showing an uninted outcome of a wrong query. – Jonas Metzler Jan 09 '23 at 13:42

1 Answers1

3

The operation you're attempting to do is called "pivot" and follows two steps:

  • conditional selection through a CASE expression 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
lemon
  • 14,875
  • 6
  • 18
  • 38
  • I have observed that the conditions corresponding to `foo` may be present in more than on row of `table2.var1`. This means taking the `MAX` or the `MIN` (aggregation) drops data that may be of interest. Are there ways to pivot certain columns/tables in a db schema without possibly dropping some values? – Johan Jan 10 '23 at 09:06
  • You can do something using window functions in that regard, for example a selection on ROW_NUMBER could help you for that. Yet you may need to know the max amount of different values of data for a single partition in advance. Or go with something more complex and less efficient. – lemon Jan 10 '23 at 10:38
  • right, thanks. I will research something like this https://stackoverflow.com/a/15745076/3755989 – Johan Jan 10 '23 at 11:54
  • 1
    This will help you handle more columns, not more rows. If you need further help with this specific case, consider opening a new post in which you make evident that corner case situation. – lemon Jan 10 '23 at 11:56
  • 1
    I considered that. And why not do it. Yeah, will post it later and throw a link to it here, so that you have a chance to elaborate :-) have a good day – Johan Jan 10 '23 at 11:57
  • https://stackoverflow.com/questions/75094585/sql-pivot-multiple-and-partially-similar-row-values-to-multiple-cols – Johan Jan 12 '23 at 10:07