SELECT以字符串形式返回多行 [英] 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

devices.fk_contractid = Contracts.contractid

我需要做出选择才能给出结果:

I need to make select which will give result:

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

如何在PL-SQL中完成?

How can that be done in PL-SQL?

推荐答案

假设11克(引入listagg时):

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

这篇关于SELECT以字符串形式返回多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！