I have a table with multiple people in the same Suite I need to match on the suite then display each person as the First Id just Once with their list of room mates.

Because their are more than just two, i am running into scenarios where i have multiple results where the First Id is listed multiple times and room mates are ordered differently or I can only bring back one row with the 4 IDs

Example:

I have a table like this:

Table Name: ROOM_LIST

ID | BUILDING | SUITE | ROOM
01 | BU_1     | SU_1  | RO_1
02 | BU_1     | SU_1  | RO_2
03 | BU_1     | SU_1  | RO_3
04 | BU_1     | SU_1  | RO_4
05 | BU_1     | SU_2  | RO_1
06 | BU_1     | SU_2  | RO_2
07 | BU_2     | SU_1  | RO_1
08 | BU_2     | SU_1  | RO_2

I tried a query like this:

select A.ID as Primary,
       B.ID as Roomate_1,
       C.ID as Roomate_2,
       D.ID as Roomate_3,
       A.BUILDING as Building,
       A.SUITE As Suite,
       A.ROOM As Room
from ROOM_LIST A
Left Join ROOM_LIST B on A.BUILDING = B.BUILDING and A.SUITE = B.SUITE
Left Join ROOM_LIST C on A.BUILDING = C.BUILDING and A.SUITE = C.SUITE
Left Join ROOM_LIST D on A.BUILDING = D.BUILDING and A.SUITE = D.SUITE
where A.ID > B.ID
and   A.ID > C.ID
and   A.ID > D.ID
and   B.ID > C.ID
and   B.ID > D.ID
and   C.ID > D.ID
order by Primary,Roomate_1,Roomate_2,Roomate_3,Building,Suite,Room;

Which gets rid of extra duplicates but I only get one row per a Suite instead of one row with each ID as the Primary.

Also tried a similar one but with <> or != instead of > and then i get multiple duplicates with one id as the first but the 2nd, 3rd, and 4th are interchanged, so they are technically not duplicates.

Which is why i say "non identical" duplicates :)

My end Results would look like this:

| Primary | Roomate_1 | Roomate_2 | Roomate_3 | Building | Suite | Room
| 01      | 02        | 03        | 04        | BU_1     | SU_1  | RO_1
| 02      | 03        | 04        | 01        | BU_1     | SU_1  | RO_2
| 03      | 04        | 01        | 02        | BU_1     | SU_1  | RO_3
| 04      | 01        | 02        | 03        | BU_1     | SU_1  | RO_4
| 05      | 06        | Null      | Null      | BU_1     | SU_2  | RO_1
| 06      | 05        | Null      | Null      | BU_1     | SU_2  | RO_2

I have tried multiple queries with different sub-select in the select statement or in the from, but i cant seem to get it down to one result per an ID as the Primary. I've looked into doing a pivot, but (my understanding) would only work if I had the same id having multiple results and i wanted to turn the multiple results into columns.

Thought about a Union, but i dont know how to do query across unions? if that is even a thing

Any assistance would be appreciated

EDIT: The below Solution only works in 12C, but I need an 11G Solution:

  with dt as (
    select 01 id , 'BU_1' building,  'SU_1' suite ,'RO_1' room from dual union all 
    select 02 id , 'BU_1' building,  'SU_1' suite ,'RO_2' room from dual union all 
    select 03 id , 'BU_1' building,  'SU_1' suite ,'RO_3' room from dual union all 
    select 04 id , 'BU_1' building,  'SU_1' suite ,'RO_4' room from dual union all 
    select 05 id , 'BU_1' building,  'SU_2' suite ,'RO_1' room from dual union all 
    select 06 id , 'BU_1' building,  'SU_2' suite ,'RO_2' room from dual union all 
    select 07 id , 'BU_2' building,  'SU_1' suite ,'RO_1' room from dual union all 
    select 08 id , 'BU_2' building,  'SU_1' suite ,'RO_2' room from dual )
    SELECT  
    A.ID as Primary,
            ( select id from (select id,rownum rn from dt  b where  a.building = b.building AND a.suite = b.suite and b.ID != a.ID  order by id ) where rn=1)  Roomate_1,
            ( select id from (select id,rownum rn from dt  b where  a.building = b.building AND a.suite = b.suite and b.ID != a.ID  order by id ) where rn=2)  Roomate_2,
            ( select id from (select id,rownum rn from dt  b where  a.building = b.building AND a.suite = b.suite and b.ID != a.ID  order by id ) where rn=3)  Roomate_3,
           a.BUILDING as Building,
           A.SUITE As Suite,
           A.ROOM As Room
    FROM
        dt a 
    order by Primary,Roomate_1,Roomate_2,Roomate_3,Building,Suite,Room  

I added following to one of the given answers: and b.ID != a.ID and changed rn=2 to rn=1 to start the count because of 0

1 Answers

1
Jignesh On Best Solutions

Not sure about performance impact. Will need to do the analysis but gives the result as expected.

12c Answer.

    with dt as (
    select 01 id , 'BU_1' building,  'SU_1' suite ,'RO_1' room from dual union all 
    select 02 , 'BU_1' building,  'SU_1' suite ,'RO_2' room from dual union all 
    select 03 , 'BU_1' building,  'SU_1' suite ,'RO_3' room from dual union all 
    select 04, 'BU_1' building,  'SU_1' suite ,'RO_4' room from dual union all 
    select 05 , 'BU_1' building,  'SU_2' suite ,'RO_1' room from dual union all 
    select 06 , 'BU_1' building,  'SU_2' suite ,'RO_2' room from dual union all 
    select 07 , 'BU_2' building,  'SU_1' suite ,'RO_1' room from dual union all 
    select 08 , 'BU_2' building,  'SU_1' suite ,'RO_2' room from dual )
    SELECT  
    A.ID as Primary,
            ( select id from (select id,rownum rn from dt  b where  a.building = b.building AND a.suite = b.suite  order by id ) where rn=2)  Roomate_1,
            ( select id from (select id,rownum rn from dt  b where  a.building = b.building AND a.suite = b.suite  order by id ) where rn=3)  Roomate_2,
            ( select id from (select id,rownum rn from dt  b where  a.building = b.building AND a.suite = b.suite  order by id ) where rn=4)  Roomate_3,
           a.BUILDING as Building,
           A.SUITE As Suite,
           A.ROOM As Room
    FROM
        dt a 
    order by Primary,Roomate_1,Roomate_2,Roomate_3,Building,Suite,Room                            

11g answer. I am not sure how much ordering of data into roommate1 to roomate3 column is necessary.

WITH  dt as (
    select 01 id , 'BU_1' building,  'SU_1' suite ,'RO_1' room from dual union all 
    select 02 id , 'BU_1' building,  'SU_1' suite ,'RO_2' room from dual union all 
    select 03 id , 'BU_1' building,  'SU_1' suite ,'RO_3' room from dual union all 
    select 04 id , 'BU_1' building,  'SU_1' suite ,'RO_4' room from dual union all 
    select 05 id , 'BU_1' building,  'SU_2' suite ,'RO_1' room from dual union all 
    select 06 id , 'BU_1' building,  'SU_2' suite ,'RO_2' room from dual union all 
    select 07 id , 'BU_2' building,  'SU_1' suite ,'RO_1' room from dual union all 
    select 08 id , 'BU_2' building,  'SU_1' suite ,'RO_2' room from dual ),
joindrslt AS (
    SELECT a.*, b.id roommate,
        ROW_NUMBER() OVER(PARTITION BY a.suite, a.building, a.room ORDER BY b.id ) AS ri
    FROM 
    dt a 
    JOIN dt b ON a.building = b.building AND a.suite = b.suite AND b.id != a.id
    ORDER BY b.id
)
SELECT ID Primary,
            roomate_1,
            roomate_2,
            roomate_3,
            Building,Suite,
            Room  FROM
    (
        SELECT
            * 
        FROM
            joindrslt PIVOT (
                MAX ( roommate )
                FOR ri
                IN ( 1 AS roomate_1, 2 AS roomate_2, 3 AS roomate_3 )
            )
    )
ORDER BY
 Primary,Roomate_1,Roomate_2,Roomate_3,Building,Suite,Room