How to get SQL result view from row to separate columns

Question

I have a complex select query that returns a result:

  1_id   2_id   3_id    4_id   CultureId
value1  value2  value3    a       1
value1  value2  value3    b       2

I need to get the result in format like that:

  1_id   2_id   3_id    4_id_1   4_id_2  
value1  value2  value3    a        b       

I was able to come up with that, but maybe there is a better way to achieve that?

SELECT 1_id, 2_id, 3_id, (SELECT 4_id from table where 1_id = value1 and CultureId = 1) as 4_id_1, 
                         (SELECT 4_id from table where 1_id = value1 and CultureId = 2) as 4_id_2 
from table where 1_id = value1  

The issue is that my actual inner SELECT query ("SELECT 1_id, 2_id, 3_id, 4_id CultureId from table where 1_id = value1") that I used three times in my solution is a complex one and contains few joins that make that solution huge.

Is there a way to simplify that?

Answer 1

You can just aggregate and use min() and max():

select id_1, id_2, id_3,
       min(cultureId) as cultureid_1,
       nullif(max(cultureId), min(cultureId)) as cultureid_2
from t
group by id_1, id_2, id_3;