xCloudx8 xCloudx8 - 3 months ago 9
SQL Question

Postgresql CASE put in the same row

I made a query useful for my need :

select distinct min(term1_id), term2_id,
case when distance = 0 then term2_id end as dist0,
case when distance = 1 then term2_id end as dist1,
case when distance = 2 then term2_id end as dist2,
case when distance = 3 then term2_id end as dist3,
case when distance = 4 then term2_id end as dist4,
case when distance = 5 then term2_id end as dist5,
case when distance = 6 then term2_id end as dist6,
case when distance = 7 then term2_id end as dist7,
case when distance = 8 then term2_id end as dist8,
case when distance = 9 then term2_id end as dist9,
case when distance = 10 then term2_id end as dist10,
case when distance = 11 then term2_id end as dist11,
case when distance = 12 then term2_id end as dist12,
case when distance = 13 then term2_id end as dist13,
case when distance = 14 then term2_id end as dist14,
case when distance = 15 then term2_id end as dist15
from graph_path
group by term2_id, distance
order by term2_id;


Put it return the results in "rows" like this:

Term1_id dist0 dist1 dist2
1 1
1 5
1 118
1 12823
1 40006
1 6
1 7


How can i put everything in one row like this?

Term1_id dist 0 dist1 dist2
1 1 5 6
1 118 7 etc...


Is there a way to make it compact? Instead of creating a row for each dist?

Answer

Use aggregation:

select term1_id, 
       max(case when distance = 0 then term2_id end) as dist0,
       max(case when distance = 1 then term2_id end) as dist1,
       max(case when distance = 2 then term2_id end) as dist2,
       max(case when distance = 3 then term2_id end) as dist3,
       max(case when distance = 4 then term2_id end) as dist4,
       max(case when distance = 5 then term2_id end) as dist5,
       max(case when distance = 6 then term2_id end) as dist6,
       max(case when distance = 7 then term2_id end) as dist7,
       max(case when distance = 8 then term2_id end) as dist8,
       max(case when distance = 9 then term2_id end) as dist9,
       max(case when distance = 10 then term2_id end) as dist10,
       max(case when distance = 11 then term2_id end) as dist11,
       max(case when distance = 12 then term2_id end) as dist12,
       max(case when distance = 13 then term2_id end) as dist13,
       max(case when distance = 14 then term2_id end) as dist14,
       max(case when distance = 15 then term2_id end) as dist15
 from graph_path
 group by term1_id
 order by term1_id;

Based on the logic in the query, I think you actually want to aggregate by term1_id.