SELECT返回多个行作为字符串

Question

我有一个表：

合同：

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 

devices.fk_contractid = contracts.contractid

我需要做出选择，这将给结果：

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

如何在PL-SQL来完成？

Answer 1

做这样的事情：

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

添加你需要的列 - 使用双栏（我似乎记得）来连接字符串。

您需要试验一下，看看如何得到一个引号 - 要么用单引号提供，要么用双引号将引号以某种方式转义。

Answer 2

假设第11G（当listagg介绍）：

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 

Answer 3

如果你不使用11g和不能使用@约翰·道尔的解决方案，那么你就可以创建自己的aggregate function。

如果你不害怕的性能问题，那么你可以尝试使用XMLAGG作为聚合函数，然后格式化，即：

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

（没有检查）

Answer 4

如果你有10g而不是11g，有一个类似于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不会让你排序。

您也可以创建自己的功能是这样的：

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; 

您应该添加代码以处理4000个字符的限制。

您的查询现在是

SELECT contract_name 
    , concat_serialnum(contractid) 
    from Contracts;