CODEWITHSUNDEEP
sqlsql-serverselectsubquery
Lia
Lia

Reputation: 526

How to get SQL result view from row to separate columns

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?

Upvotes: 4

Views: 84

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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;

Upvotes: 3

Related Questions