All Forums Database
808healthcare 1 post Joined 09/16
07 Sep 2016
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

AtardecerR0j0 71 posts Joined 09/12
08 Sep 2016
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

 

Be More!!

You must sign in to leave a comment.