Untitled

SELECT returning multiple rows as string
contractid | contract name
"1" | "MAG:001"
"2" | "MAG:002"

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

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

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

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

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;

SELECT contract_name
     , concat_serialnum(contractid)
  from Contracts;

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

select contract_name,
       cast(xmltransform(xmlelement("a",
                                    xmlagg(xmlelement("serialNum", serialnum))),
                         xmltype('<?xml version="1.0" ?><xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/"><xsl:for-each select="//serialNum"><xsl:value-of select="."/>, </xsl:for-each></xsl:template></xsl:stylesheet>')) as
            varchar2(4000)) as serialnums
  from contracts, devices
 where contractId = fk_contractid
 group by contract_name