- 3,723,943 Users
- 2,244,655 Discussions
- 7,850,771 Comments
Forum Stats
Discussions
Categories
- 16 Data
- 362.2K Big Data Appliance
- 7 Data Science
- 2.1K Databases
- 618 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 32 Multilingual Engine
- 497 MySQL Community Space
- 7 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 422 SQLcl
- 62 SQL Developer Data Modeler
- 185.1K SQL & PL/SQL
- 21.1K SQL Developer
- 2.4K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.6K Development Tools
- 14 DevOps
- 3K QA/Testing
- 337 Java
- 10 Java Learning Subscription
- 12 Database Connectivity
- 72 Java Community Process
- 2 Java 25
- 12 Java APIs
- 141.2K Java Development Tools
- 8 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 16 Java SE
- 13.8K Java Security
- 4 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 147 LiveLabs
- 34 Workshops
- 10 Software
- 4 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 4 Deutsche Oracle Community
- 16 Español
- 1.9K Japanese
- 3 Portuguese
How can I group a column one or more results in the same column?

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
-
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.
Answers
-
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.
-
Wow! That did the trick. Thanks so much! I did not realize it would be something as simple as that.