Joining multiple rows into a single row without aggregation using Oracle11g

Question

The below link shows the same question that was asked which I want to ask and the code which was provided to answer the query, I used this query (modified it) but please how do I add a join table and a where clause in this query? see below the code I have added to the code in the link below but I am getting error messages. I would like the output to be same as the one provided in the link below

Joining multiple rows into a single row without aggregation [Oracle]

Bare in mind that the desired result provided in the link is my same desired results.

Below is the script which I have modified:

select max(case when seqnum = 1 then p.PERSONID end) as PersonID,
       max(case when seqnum = 1 then t.PHONEID end) as PhoneID1,
       max(case when seqnum = 1 then t.PHONENUM end) as PhoneNum1,
       max(case when seqnum = 1 then t.TYPE end) as Type1,
       max(case when seqnum = 1 then t.ISPRIMARY end) as IsPrimary1,
       max(case when seqnum = 1 then t.ROWSTAMP1 end) as Rowstamp1,
       max(case when seqnum = 2 then t.PHONEID end) as PhoneID2,
       max(case when seqnum = 2 then t.PHONENUM end) as PhoneNum2,
       max(case when seqnum = 2 then t.TYPE end) as Type2,
       max(case when seqnum = 2 then t.ISPRIMARY end) as IsPrimary2,
       max(case when seqnum = 2 then t.ROWSTAMP1 end) as Rowstamp2,
       max(case when seqnum = 3 then t.PHONEID end) as PhoneID3,
       max(case when seqnum = 3 then t.PHONENUM end) as PhoneNum3,
       max(case when seqnum = 3 then t.TYPE end) as Type3,
       max(case when seqnum = 3 then t.ISPRIMARY end) as IsPrimary3,
       max(case when seqnum = 3 then t.ROWSTAMP1 end) as Rowstamp3
      from (test1.phone t left join test2.person p  
            ON t1.PERSONID = t2.PERSONID
      where t2.PERSONID = 'MXSDFD'
      ) t;
      rownum as seqnum
      from t

Please [edit] your question with a [MRE] including: the DDL statements for your tables; DML statements for some sample data (both of which as text that we can copy/paste and execute rather than as images); and your expected output. Please help us to help you by giving us all the information necessary to answer the question. Also, your SQL query is syntactically invalid and won't run and your title doesn't make sense compared to the question as you say "without aggregation" and then use aggregation throughout your query. — MT0, Oct 15 '20 at 10:25

score 0 · Answer 1 · answered Oct 15 '20 at 10:39

Use PIVOT:

select personid,
       "1_PHONEID"   AS phoneid1,
       "1_PHONENUM"  AS phonenum1,
       "1_TYPE"      AS type1,
       "1_ISPRIMARY" AS isprimary1,
       "1_ROWSTAMP1" AS rowstamp1,
       "2_PHONEID"   AS phoneid2,
       "2_PHONENUM"  AS phonenum2,
       "2_TYPE"      AS type2,
       "2_ISPRIMARY" AS isprimary2,
       "2_ROWSTAMP1" AS rowstamp2,
       "3_PHONEID"   AS phoneid3,
       "3_PHONENUM"  AS phonenum3,
       "3_TYPE"      AS type3,
       "3_ISPRIMARY" AS isprimary3,
       "3_ROWSTAMP1" AS rowstamp3
from   (
  SELECT p.personid,
         t.phoneid,
         t.phonenum,
         t.type,
         t.isprimary,
         t.rowstamp1,
         ROW_NUMBER() OVER (
           PARTITION BY p.personid ORDER BY t.isprimary DESC, t.rowstamp1 DESC
         ) AS seqnum
  FROM   person p
         left join phone t   
         ON ( t.PERSONID = p.PERSONID )
  where  t.PERSONID = 'MXSDFD'
)
PIVOT (
  MAX( phoneid ) AS phoneid,
  MAX( phonenum ) AS phonenum,
  MAX( type ) AS type,
  MAX( isprimary ) AS isprimary,
  MAX( rowstamp1 ) AS rowstamp1
  FOR seqnum IN ( 1, 2, 3 )
)

Which, for the sample data:

CREATE TABLE person ( personid, name ) AS
SELECT 'MXSDFD', 'Alice' FROM DUAL;

CREATE TABLE phone ( personid, phonenum, phoneid, type, isprimary, rowstamp1 ) AS
SELECT 'MXSDFD', '012346', 1, 'A', 'N', TIMESTAMP '2020-10-13 09:00:00' FROM DUAL UNION ALL
SELECT 'MXSDFD', '555666', 3, 'C', 'N', TIMESTAMP '2020-10-13 08:30:00' FROM DUAL UNION ALL
SELECT 'MXSDFD', '987654', 2, 'B', 'Y', TIMESTAMP '2020-10-13 08:00:00' FROM DUAL;

Outputs:

PERSONID | PHONEID1 | PHONENUM1 | TYPE1 | ISPRIMARY1 | ROWSTAMP1                    | PHONEID2 | PHONENUM2 | TYPE2 | ISPRIMARY2 | ROWSTAMP2                    | PHONEID3 | PHONENUM3 | TYPE3 | ISPRIMARY3 | ROWSTAMP3                   
:------- | -------: | :-------- | :---- | :--------- | :--------------------------- | -------: | :-------- | :---- | :--------- | :--------------------------- | -------: | :-------- | :---- | :--------- | :---------------------------
MXSDFD   |        2 | 987654    | B     | Y          | 13-OCT-20 08.00.00.000000000 |        1 | 012346    | A     | N          | 13-OCT-20 09.00.00.000000000 |        3 | 555666    | C     | N          | 13-OCT-20 08.30.00.000000000

db<>fiddle here

Is there Any way we can make the 1st select statement dynamic in case we get more rows it wont limit to just 3. How do i go about it please can you advice, I have tried several ways. This is the Select statement (from the script) I mean to make dynamic: "1_PHONEID" AS phoneid1, "1_PHONENUM" AS phonenum1, "1_TYPE" AS type1, "1_ISPRIMARY" AS isprimary1, "1_ROWSTAMP1" AS rowstamp1, "2_PHONEID" AS phoneid2, "2_PHONENUM" AS phonenum2, "2_TYPE" AS type2, "2_ISPRIMARY" AS isprimary2, — Yemisi Adeoluwa, Oct 20 '20 at 08:45
@YemisiAdeoluwa You can use `SELECT * FROM ...` and accept the default column names; I only used that method to get the names to be the ones that you had put in your question. — MT0, Oct 20 '20 at 08:51
@YemisiAdeoluwa [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=a73df2b9ccdab52d497f0444c8b4547a) — MT0, Oct 20 '20 at 09:07
@YemisiAdeoluwa Short answer to making a dynamic pivot (using either the `PIVOT` expression or conditional aggregation) is that, no, it is not possible. You either need to use `PIVOT XML` or dynamic SQL (see [https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql](https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql)). — MT0, Oct 20 '20 at 09:19

Gordon Linoff · Answer 2 · 2020-10-15T18:24:54.683

0

You seem to want logic like this:

select max(case when seqnum = 1 then t.PERSONID end) as PersonID,
       max(case when seqnum = 1 then t.PHONEID end) as PhoneID1,
       max(case when seqnum = 1 then t.PHONENUM end) as PhoneNum1,
       max(case when seqnum = 1 then t.TYPE end) as Type1,
       max(case when seqnum = 1 then t.ISPRIMARY end) as IsPrimary1,
       max(case when seqnum = 1 then t.ROWSTAMP1 end) as Rowstamp1,
       max(case when seqnum = 2 then t.PHONEID end) as PhoneID2,
       max(case when seqnum = 2 then t.PHONENUM end) as PhoneNum2,
       max(case when seqnum = 2 then t.TYPE end) as Type2,
       max(case when seqnum = 2 then t.ISPRIMARY end) as IsPrimary2,
       max(case when seqnum = 2 then t.ROWSTAMP1 end) as Rowstamp2,
       max(case when seqnum = 3 then t.PHONEID end) as PhoneID3,
       max(case when seqnum = 3 then t.PHONENUM end) as PhoneNum3,
       max(case when seqnum = 3 then t.TYPE end) as Type3,
       max(case when seqnum = 3 then t.ISPRIMARY end) as IsPrimary3,
       max(case when seqnum = 3 then t.ROWSTAMP1 end) as Rowstamp3
from (select t.*, rownum as seqnum
      from test1.phone t join
           test2.person p  
           on t.PERSONID = p.PERSONID
      where p.PERSONID = 'MXSDFD'
     ) t;

That said, you don't seem to need the join at all, because you are filtering on the join key (it is in both tables). So:

from (select t.*, rownum as seqnum
      from test1.phone t 
      where t.PERSONID = 'MXSDFD'
     ) t

edited Oct 15 '20 at 18:24

answered Oct 15 '20 at 10:40

Gordon Linoff

1,242,037
58
646
786

Thanks for helping out with it, much appreciated. I am still getting error message. though.it says : ORA-00904: "P"."PERSONID": invalid identifier it would be nice to have a second try, if that is okay with you. i have had several try with no success. Thank you – Yemisi Adeoluwa Oct 15 '20 at 16:19
@YemisiAdeoluwa The `p` table alias are only visible inside the sub-query. What you need to do is change `p.PERSONID` to `t.PERSONID` in the first line. – MT0 Oct 15 '20 at 17:36
@YemisiAdeoluwa . . . That should be `t.personid`. They have the same value. – Gordon Linoff Oct 15 '20 at 18:25
@GordonLinoff yes, correct it now works. thanks. May I ask if there is a way to make it dynamic so if there are more than three rows it would not limit to just 3 but would show the rest. Example, the output only shows PhoneID1, PhoneID2, PhoneID3 what if there was PhoneID 5,6,7 it would not appear in the result because it has been limited to 1, 2,3 I have tried several ways. – Yemisi Adeoluwa Oct 20 '20 at 08:58
@YemisiAdeoluwa . . . You would have to use dynamic SQL and that would be a bit more complicated -- and a different question. – Gordon Linoff Oct 20 '20 at 13:02

Joining multiple rows into a single row without aggregation using Oracle11g

2 Answers2