Oracle simple list results to comma separated list with quotes?

user_648026 0
Viewed 316

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');
oracle11g oracle sql
1 Answers
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

Justin_Cave_103970
Related