CODEWITHSUNDEEP
sqloracle-databaseoracle11g
CCC
CCC

Reputation: 121

join or concatenate values of a single column whose data type is NUMBER as a single string in Oracle

Table structure

|--------------------------------| |column name | column data type | |------------ |------------------| |branchid |NUMBER(38) | |--------------------------------|

My SQL statement

SELECT branchid FROM branchconfigs where branchconfigtypeid = (select branchconfigtypeid from branchconfigtypes where description = 'Show Branch Address');

Above query's output

branchid

199 204 2850

My goal

To return all branchids as a single string separated by comma as shown below:

'199,204,2850'

My attempt

with rws as ( select (SELECT to_char(branchid) FROM branchconfigs where branchconfigtypeid = (select branchconfigtypeid from branchconfigtypes where description = 'Show Branch Address')) as branchid from ((SELECT to_char(branchid) FROM branchconfigs where branchconfigtypeid = (select branchconfigtypeid from branchconfigtypes where description = 'Show Branch Address'))) connect by level <= (SELECT count(branchid) FROM branchconfigs where branchconfigtypeid = (select branchconfigtypeid from branchconfigtypes where description = 'Show Branch Address')) ) select listagg(branchid, ',') within group (order by branchid) from rws;

Error found after executing above query

ORA-01427: single-row subquery returns more than one row

Expected output

'199,204,2850'

Any help, dear members ?

Thanks in advance.

Upvotes: 0

Views: 41

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520938

I would rewrite your query to use a join instead of a subquery. Then, use LISTAGG to rollup the branch IDs into a single CSV string:

SELECT LISTAGG(bc.branchid, ',') WITHIN GROUP (ORDER BY bc.branchid) branchids
FROM branchconfigs bc
INNER JOIN branchconfigtypes bct
    ON bc.branchconfigtypeid = bct.branchconfigtypeid
WHERE
    bct.description = 'Show Branch Address';

Upvotes: 1

Related Questions