sql - 如何在sql的同一行中获取不同的记录？

Question

我有一个类似于下面给出的表结构

1001    Jon
1002    Jacob
1003    Cayle

我想以某种形式从表中获取记录

1001 – Jon,   1002 – Jacob,  1003 – Cayle

任何人都可以建议一些简单的方法。

Answer 1

Oracle上最简单的方法是 listagg 。尝试这样的事情:

create table parameter as
select 1001 as id, cast('Jon' as varchar2(10)) as name from dual union all
select 1002, 'Jacob' from dual union all
select 1003, 'Cayle' from dual 
;

select listagg(id||' - '||name, ', ') within group (order by id) as list
from parameter
;

LIST                                                                           
------------------------------------------
1001 - Jon, 1002 - Jacob, 1003 - Cayle   

Oracle的另一个解决方案。我认为很容易移植到其他RDBMS:

select 
    rtrim(
        xmlagg(
            хmlElement(tag, id||' - '||name, ', ').еxtract('//text()') order by id
        ).getStringVal(), ', ') as list
from parameter
;

LIST                                                                           
-------------------------------------------
1001 - Jon, 1002 - Jacob, 1003 - Cayle            

如果表真的很小，则可以定义特定的函数。此功能可以轻松移植到任何RDBMS:

create or replace function paramList return varchar2 is
    ret  varchar2(1024) := null;
begin
    for row_ in (select id||' - '||name as tag from parameter) loop
        ret := ret||', '||row_.tag;
    end loop;
    return ltrim(ret, ', ');
end paramlist;
/
show errors

col parameters format a40
select paramList parameters from dual;

PARAMETERS                             
----------------------------------------
1001 - Jon, 1002 - Jacob, 1003 - Cayle  

或更通用的功能:

create or replace function concatRows (cursor_ sys_refcursor) return  varchar2 is
    ret  varchar2(32767); 
    tmp  varchar2(32767);
begin
    loop fetch cursor_ into tmp;
        exit when cursor_%notfound;
        ret := ret || ', ' || tmp;
    end loop;
    return ltrim(ret, ', ');
end ;
/

select concatRows(cursor(
    select id||' - '||name from parameter
)) parameters from dual
;

PARAMETERS                             
----------------------------------------
1001 - Jon, 1002 - Jacob, 1003 - Cayle  

另一个带有 collect的通用函数:

create or replace function arrayagg (arr sys.ODCIVarchar2List, delimiter varchar2 default ', ') return varchar2 is
    ret varchar2(32767);
begin
    for i in arr.first.. arr.last loop
        ret := ret||arr(i)||delimiter;
    end loop;
    return rtrim(ret, delimiter);
end arrayagg;
/

select arrayagg(cast(collect(id||' - '||name) as sys.ODCIVarchar2List)) parameters 
from parameter
;

PARAMETERS                                                                     
-----------------------------------------
1001 - Jon, 1002 - Jacob, 1003 - Cayle