By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table c(
ID int,
Stage varchar(20),
StageNum int,
sDate Date);
insert into c values
(104,'Released',10,'2022-02-07'),
(104,'Slab', 20,'2022-02-18'),
(104,'Frame', 30,'2022-03-07'),
(104,'Mechanicals',42,'2022-03-10'),
(105,'Released', 10,'2022-02-07'),
(105,'Slab', 20,'2022-02-18'),
(106,'Released',10,'2022-02-07'),
(106,'Slab', 20,'2022-02-18'),
(106,'Frame', 30,'2022-03-04'),
(106,'Cornice', 40,'2022-03-08');
10 rows affected
select * from c;
| ID | Stage | StageNum | sDate |
|---|---|---|---|
| 104 | Released | 10 | 2022-02-07 |
| 104 | Slab | 20 | 2022-02-18 |
| 104 | Frame | 30 | 2022-03-07 |
| 104 | Mechanicals | 42 | 2022-03-10 |
| 105 | Released | 10 | 2022-02-07 |
| 105 | Slab | 20 | 2022-02-18 |
| 106 | Released | 10 | 2022-02-07 |
| 106 | Slab | 20 | 2022-02-18 |
| 106 | Frame | 30 | 2022-03-04 |
| 106 | Cornice | 40 | 2022-03-08 |
select
id,
string_agg(stageNUM,'-')
from c
group by id
| id | (No column name) |
|---|---|
| 104 | 10-20-30-42 |
| 105 | 10-20 |
| 106 | 10-20-30-40 |
with stages as(
select
id,
string_agg(stageNUM,'') s
from c
group by id
)
select
id,
case when s = '1020304042'
then 'Mechanicals'
when left(s,8) = '10203040'
then 'Cornice'
when left(s,6) = '102030'
then 'Frame'
when left(s,4) = '1020'
then 'Slab'
when left(s,2) = '10'
then 'Released'
else 'unknown' end Stage
from stages
| id | Stage |
|---|---|
| 104 | Frame |
| 105 | Slab |
| 106 | Cornice |