DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How can I group a column one or more results in the same column?

User_OMEF8Dec 4 2020

I have a query that returns multiple rows. There is one column that I want to concatenate into the same column. I have a query that can return the result below.

ID | FirstName | LastName | City | State | Phone | SystemDate
1 | John | Doe | ABC | FL | 5555555555 | 2020-12-01
1 | John | Doe | ABC | FL | 8888888888 | 2020-12-01
2 | Jane | Smith | XYZ | CA | 9999999999 | 2020-12-01

What I would like to do is have the output look like this instead.

ID | FirstName | LastName | City | State | Phone | SystemDate
1 | John | Doe | ABC | FL | 5555555555, 8888888888 | 2020-12-01
2 | Jane | Smith | XYZ | CA | 9999999999 | 2020-12-01

Here is my query that can do the first output.
select a.idref, p.firstname, p.lastname, d.city, d.state, m.phone, m.createdate
from master m
join demographics d on m.did = d.id
join commoninfo c on d.eid = c.id
join acounts a on c.aid = a.id
join people p on a.pid = p.id
where m.createdate >= trunc(sysdate + interval '-1' day);

Any tips or hints for this newbie is greatly appreciated. Thanks in advance.

This post has been answered by Solomon Yakobson on Dec 4 2020

Jump to Answer

Solomon Yakobson

Answer

WITH SAMPLE(ID,FirstName,LastName,City,State,Phone,SystemDate)
  AS (
      SELECT 1,'John','Doe','ABC','FL','5555555555',DATE '2020-12-01' FROM DUAL UNION ALL
      SELECT 1,'John','Doe','ABC','FL','8888888888',DATE '2020-12-01' FROM DUAL UNION ALL
      SELECT 2,'Jane','Smith','XYZ','CA','9999999999',DATE '2020-12-01' FROM DUAL
     )
SELECT  ID,
        FirstName,
        LastName,
        City,
        State,
        LISTAGG(Phone,',') WITHIN GROUP(ORDER BY Phone) Phone,
        SystemDate
  FROM  SAMPLE
  GROUP BY ID,
           FirstName,
           LastName,
           City,
           State,
           SystemDate
/

        ID FIRS LASTN CIT ST PHONE                 SYSTEMDAT
---------- ---- ----- --- -- --------------------- ---------
         1 John Doe   ABC FL 5555555555,8888888888 01-DEC-20
         2 Jane Smith XYZ CA 9999999999            01-DEC-20


SQL>

SY.

Marked as Answer by User_OMEF8 · Dec 5 2020

1 person found this helpful

User_OMEF8

Wow! That did the trick. Thanks so much! I did not realize it would be something as simple as that.

DevOps, CI/CD and Automation

How can I group a column one or more results in the same column?

Comments

Post Details