Reputation: 375
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
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
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;
Upvotes: 7