SELECT returning multiple rows as string

Question

I have a table:

Contracts:

contractid | contract name
"1" | "MAG:001"
"2" | "MAG:002"

-- and --

Devices:

devid | serialnum | fk_contractid
10 | 1234 | 1
11 | 5678 | 1
12 | 4321 | 2
13 | 8765 | 2

devices.fk_contractid = contracts.contractid

I need to make select which will give result:

"MAG:001" | 1234, 5678
"MAG:002" | 4321, 8765

How can that be done in PL-SQL?

Does the column 'contract name' really have a space in it, btw? :) — halfer, Feb 20 '12 at 09:56

score 4 · Answer 1 · answered Feb 20 '12 at 10:15

4

Assuming 11g (when listagg was introduced):

  select    CONTRACT_NAME
         || '|'
         || LISTAGG(D.SERIALNUM, ',') within group (order by CONTRACTID)
    from CONTRACTS C join DEVICES D on D.FK_CONTRACTID = C.CONTRACTID
group by CONTRACT_NAME

answered Feb 20 '12 at 10:15

John Doyle

7,475
5
33
40

score 3 · Answer 2 · answered Feb 20 '12 at 15:39

If you have 10g instead of 11g, there's a hidden function that's similar to LISTAGG.

WMSYS.WM_CONCAT

SELECT Contracts.contract_name
     , WMSYS.WM_CONCAT(Devices.serialnum)
  FROM Contracts, Devices
 WHERE Contracts.contractid = Devices.fk_contractid
 GROUP BY Contracts.contract_name;

WM_CONCAT does not let you sort.

You can also create your own function like this:

FUNCTION concat_serialnum(the_contract Contracts.contractid%TYPE)
 RETURN VARCHAR2
IS
  return_value VARCHAR2(4000);
  CURSOR serials_cur IS
    SELECT serialnum
      FROM Devices
      WHERE contractid = the_contract
      ORDER BY serialnum;
BEGIN
  FOR serials_rec IN serials_cur LOOP
    return_value := return_value || ', ' || serials_rec.serialnum;
  END LOOP;
  RETURN LTRIM(return_value, ', ');
END concat_serialnum;

You should add code to handle the 4,000 character limit.

Your query would now be

SELECT contract_name
     , concat_serialnum(contractid)
  from Contracts;

+1, but you shouldn't need to specify the schema when using `wm_concat`. I **believe** there's a public synonym on it like all the `dbms_...` functions. — Ben, Feb 20 '12 at 20:13

score 0 · Answer 3 · answered Feb 20 '12 at 09:52

0

Do something like this:

SELECT '"' || c.contract_name || '"'
...
FROM contracts c INNER JOIN devices d ON d.fk_contractid = c.contractid

Add in the columns you need - using the double bar (I seem to recall) to concatenate strings.

You'll need to experiment to see how to get a quote mark - either supply it in single quotes, or in double quotes with the quote mark escaped in some way.

answered Feb 20 '12 at 09:52

halfer

19,824
17
99
186

Bah, I just noticed you want multiple rows in the same string. There should be multiple-row functions in the Oracle docs. – halfer Feb 20 '12 at 09:57

A.B.Cade · Answer 4 · 2012-05-09T10:01:03.500

If you're not using 11g and can't use @John Doyle's solution, then you can create your own aggregate function.

If you're not afraid of performance issues then you can try to use xmlagg as an aggregate function and then format it, ie:

select contract_name,
       rtrim(xmlagg(xmlelement(e, serialnum || ',')).extract('//text()')) as serialnums
  from contracts, devices
 where contractId = fk_contractid
 group by contract_name

(didn't check it)

SELECT returning multiple rows as string

4 Answers4

Linked