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!

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

User_OMEF8
User_OMEF8 Member Posts: 126 Blue Ribbon

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.

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

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
  • User_OMEF8
    User_OMEF8 Member Posts: 126 Blue Ribbon

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

Sign In or Register to comment.