CODEWITHSUNDEEP
sqlpostgresql
Ms workaholic
Ms workaholic

Reputation: 393

How to combine two rows under specific condition

I have following table:

CREATE table table1 (id int , cd date, ct TIME, co text)

INSERT INTO table1 
VALUES (0, '1/1/2018', '12:00:00', 'B'), 
        (1, '1/1/2018', '12:30:00', 'BC'), 
        (2, '1/12/2018', '12:00:00', 'B'), 
        (3, '1/22/2018', '12:00:00', 'BC')

I need to combine "co" column when values of "cd" and "ct" columns are the same for 'B' AND 'BC' or only values of "ct" are different and display values of record 'B' for 'B-BC'. for the above table1 records I need following result:

"id"    "cd"    "ct"    "co"
"0" "1/1/2018"  "12:00:00 PM"   "B-BC"
"2" "1/12/2018" "12:00:00 PM"   "B"
"3" "1/22/2018" "12:00:00 PM"   "BC"

What is the most effective way to do that in postgresql 8.4?

Upvotes: 2

Views: 41

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

You could use string_agg:

SELECT cd, MIN(id) AS id, min(ct) AS ct, string_agg(co, '-' ORDER BY ct) AS co
FROM table1
GROUP BY cd;

DBFiddle Demo

EDIT:

Lovely answer! but ct for 'B' is not always minimum value. I need the ct, which is specifically in record 'B', which may be greater 'ct' for 'BC' or less

You could use windowed function:

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY cd ORDER BY ct ASC) AS rn FROM table1
)
SELECT id, cd, ct,s.co
FROM cte
JOIN LATERAL(SELECT string_agg(co, '-' ORDER BY ct) AS co 
             FROM cte c2 WHERE c2.cd=cte.cd)s ON TRUE
WHERE rn=1;

DBFiddle Demo2

Upvotes: 0

jose_bacoy
jose_bacoy

Reputation: 12684

I created two common table expressions (CTE) to come up with a list of B and BC records that will be combined. The logic is get B record and BC record. Then do a left join from table1 to cte_A table and left join to cte_B table but cte_b.id_del is null. This will remove the id found in cte_B. Lastly, do a case when to use a new co value (B-BC) for id found in cte_A table. See demo here: http://sqlfiddle.com/#!15/62caa/49

with cte_a as (select a.id as id_keep 
    from table1 a 
    inner join table1 b on a.cd=b.cd
     and a.co='B' and b.co='BC') 
,cte_b  as (select b.id as id_del 
    from table1 a 
    inner join table1 b on a.cd=b.cd
    and a.co='B' and b.co='BC')
select t1.id, 
       t1.cd, 
       t1.ct, 
       case when cte_a.id_keep is null 
            then t1.co 
            else 'B-BC' end as co 
from table1 t1
left join cte_a 
on t1.id=cte_a.id_keep
left join cte_b
on t1.id = cte_b.id_del
where cte_b.id_del is null;

Upvotes: 1

Related Questions