SQL Query to fetch the multiple values that too in quotes as a single string

Toad for Oracle & DevOps Toolkit
1

Hi Experts,

Can anyone share a query which fetches the outputs of multiple Accounts as a single value that too in single quotes.

Example, we have accounts 42201,43001,48946… I want a sql query which fetches the output as

’42201’,‘43001’,'48946’

Can anyone one has idea on how to achieve this? We want all this in single column(single string)

2

Hi,

try listtagg function.

Ex:

select listagg(’’’ || no || ‘’’, ‘,’) whitin group (order by no) from the_table;

BR

fdi

3

Hi Frank,

Could you please help me 3 or 4 accounts. I tried as below, it is working. But, is there any other way to reduce the query? Because we have almost 15 accounts (those may increase, but not reduce).

WITH x (id, account_no) AS (
select 1, ‘42643’ FROM dual UNION ALL
select 1, ‘22341’ FROM dual UNION ALL
select 1, ‘32347’ FROM dual UNION ALL
select 1, ‘21012’ FROM dual UNION ALL
select 1, ‘99999’ FROM dual UNION ALL
select 1, ‘11224’ FROM dual UNION ALL
select 1, ‘32123’ FROM dual
)
SELECT id,
LISTAGG(’’’’||account_no||’’’’, ‘,’) WITHIN GROUP (ORDER BY account_no) accounts
FROM x
GROUP BY id

4

Hi Frank,

The below I created. Seems it is ok now :slight_smile:

select listagg(’’’’||acct.segment3||’’’’,’,’) within group( order by acct.segment3) accounts
from (select DISTINCT glcc1.segment3 segment3
from gl_code_combinations glcc1
where 1=1
and glcc1.enabled_flag=‘Y’
and glcc1.segment3 IN (‘25100’,‘25110’,‘25120’,‘25140’)
) acct