krishnakant
krishnakant

Reputation: 375

String formatting using LISTAGG in Oracle. Escaping single quote ` ' `

How can I format the output of listagg in Oracle to produce output(every field in the single quote) as 'student1', 'student2', 'student3'.
I have gone through documentation and other question on listagg but can't find much. SQL Query to concatenate column values from multiple rows in Oracle

SELECT LISTAGG(student_name,',')  WITHIN GROUP (ORDER BY student_name)
from students

Thanks

Upvotes: 3

Views: 5309

Answers (2)

clinomaniac
clinomaniac

Reputation: 2218

This should do the job. You need to escape the ' in the query.

SELECT LISTAGG('''' || student_name || '''',', ') WITHIN GROUP (ORDER BY student_name)
FROM students

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175606

You could use:

SELECT LISTAGG('''' || student_name || '''',',') 
       WITHIN GROUP (ORDER BY student_name)
FROM students;

or using ENQUOTE_LITERAL function:

SELECT LISTAGG(DBMS_ASSERT.ENQUOTE_LITERAL(student_name),',') 
       WITHIN GROUP (ORDER BY student_name) AS r
FROM students;

DBFiddle Demo

Upvotes: 7

Related Questions