CODEWITHSUNDEEP
sqloracle-database
user6800052
user6800052

Reputation:

how to concate multiple rows obtained as result of single query and also append single quote before and after each value

I have a table as admin_emp_leave_header which contains a column as START_DATE.

Suppose I use the following query to get those dates as:

select START_DATE sd 
from admin_emp_leave_header;

I get following result

SD
--------
01-01-2017
02-01-2017
03-01-2017
04-01-2017

But I want the output as 

SD
----------------------------------------------------
'01-01-2017','02-01-2017','03-01-2017','04-01-2017'

How do I get this using oracle?

Upvotes: 1

Views: 51

Answers (1)

pablomatico
pablomatico

Reputation: 2242

You can convert a DATE to text using the TO_CHAR function and then concatenate these strings using the LISTAGG function:

SELECT LISTAGG(TO_CHAR(start_date,'''MM-DD-YYYY'''),',') WITHIN GROUP (ORDER BY start_date)
FROM admin_emp_leave_header

If you also needed to get the current date with the same format in case the previous query didn't return any records, you just need to use NVL:

SELECT NVL(LISTAGG(TO_CHAR(start_date,'''MM-DD-YYYY'''),',') WITHIN GROUP (ORDER BY start_date),TO_CHAR(SYSDATE,'''MM-DD-YYYY'''))
FROM admin_emp_leave_header

Upvotes: 1

Related Questions