CODEWITHSUNDEEP
sqloracle-databaseoracle11g
JavaSheriff
JavaSheriff

Reputation: 7665

Oracle simple list results to comma separated list with quotes?

I would like to get results of this simple query

select PersonID from Persons where city ='Miami'

as a flat comma separated list with quotes.

desired results should be '3','5','7','8'

http://sqlfiddle.com/#!4/95e86d/1/0

I tried list_add but im getting: ORA-00904: "STRING_AGG": invalid identifier

CREATE TABLE Persons (
    PersonID NUMBER,
    FirstName varchar(255),
    City varchar(255)
);
    
    
      
INSERT INTO Persons (PersonID, FirstName, City)
  VALUES (1, 'Tom B.','Stavanger');
    
INSERT INTO Persons (PersonID, FirstName, City)
  VALUES (2, 'Jerry M.','Train City');
    
INSERT INTO Persons (PersonID, FirstName, City)
  VALUES (3, 'Eric g.','Miami');
      
INSERT INTO Persons (PersonID, FirstName, City)
  VALUES (4, 'Bar Y.','Manhattan');
      
INSERT INTO Persons (PersonID, FirstName, City)
  VALUES (5, 'John K.','Miami');
      
INSERT INTO Persons (PersonID, FirstName, City)
  VALUES (6, 'Foo F.','Washington');
      
INSERT INTO Persons (PersonID, FirstName, City)
  VALUES (7, 'Alen D.','Miami');
      
INSERT INTO Persons (PersonID, FirstName, City)
  VALUES (8, 'John K.','Miami');

Upvotes: 2

Views: 1069

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

select listagg( '''' || PersonID || '''', ',' ) 
         within group (order by personID) 
  from Persons 
 where city ='Miami'

should work.

I'd have to question why you're trying to produce this particular result. If you're going to generate this string so that you can subsequently use it in the IN list of another dynamically generated SQL statement, you're probably better off taking a step back because there are much better ways to solve the problem.

SQL Fiddle example

Upvotes: 2

Related Questions