Forum Stats

  • 3,723,943 Users
  • 2,244,655 Discussions
  • 7,850,771 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Log In Register

Categories

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

User_OMEF8
User_OMEF8 Member Posts: 126 Blue Ribbon
in Reports

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.

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,211 Black Diamond
    Accepted 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.

    User_OMEF8
    · Share on TwitterShare on Facebook

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,211 Black Diamond
    Accepted 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.

    User_OMEF8
    · Share on TwitterShare on Facebook
  • User_OMEF8
    User_OMEF8 Member Posts: 126 Blue Ribbon
    https://community.oracle.com/tech/developers/discussion/comment/16777509#Comment_16777509

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

    · Share on TwitterShare on Facebook
Sign In or Register to comment.