Collapse one to many into one SQL query result row

Database

Collapse one to many into one SQL query result row

I would like to collapse records from a one to many relationship down into a single query result row, a task can have one or two resources assigned (there is no limit on the number of resources that can be assigned but for this exercise let’s say the number can never be zero or more than two)

Task Table

Task_ID                  Resource_ID

123                         444

123                         555

678                         999

Resource Table

Resource_ID           Resource_Name

444                         Pat

555                         Chris

999                         Sam

Ideally I would like my query result to return the following:

Task_ID                  Resource1           Resource2

123                         Pat                      Chris

678                         Sam

1 REPLY
Teradata Employee

Re: Collapse one to many into one SQL query result row

create multiset volatile table Task_Table
(
Task_ID integer,
Resource_ID integer
)primary index( Task_ID )
on commit preserve rows;

insert into Task_Table values(123, 444);
insert into Task_Table values(123, 555);
insert into Task_Table values(678, 999);

create multiset volatile table Resource_Table
(
Resource_ID integer,
Resource_Name varchar(50)
)primary index( Resource_ID )
on commit preserve rows;

insert into Resource_Table(444, 'Pat');
insert into Resource_Table(555, 'Chris');
insert into Resource_Table(999, 'Sam');

select
TT.Task_id,
TT.Resource_ID,
RT.resource_name,
row_number() over(partition by TT.task_id order by TT.resource_id) as Orden
from Task_Table TT
inner join Resource_Table RT
on TT.resource_id = RT.resource_id

select Task_id,
max(case when orden=1 then Resource_Name else '' end) as Resource1,
max(case when orden=2 then Resource_Name else '' end) as Resource2
from (
select
TT.Task_id,
TT.Resource_ID,
RT.resource_name,
row_number() over(partition by TT.task_id order by TT.resource_id) as Orden
from Task_Table TT
inner join Resource_Table RT
on TT.resource_id = RT.resource_id)as TMP
group by 1