How to fetch different records in same row in sql?

Question

I have a table structure similar to one given below

1001    Jon
1002    Jacob
1003    Cayle

I want to fetch records from table in a way that it should look like

1001 – Jon,   1002 – Jacob,  1003 – Cayle

Can anybody suggest some easy way.

Are you using Oracle database or MS SQL Server? Please remove the wrong tags from your question. — Mureinik, Jun 04 '17 at 08:13
If do you want all returned records in one row, then imagine result, when you have for example 100 000 records — Oto Shavadze, Jun 04 '17 at 08:23
Yes all returned records in one row. It is a small parameter table with 20 records for now — RanchiRhino, Jun 04 '17 at 08:24
and I can cope with any version with query be it sql-server or oracle so..... — RanchiRhino, Jun 04 '17 at 08:25
https://stackoverflow.com/questions/17239323/oracle-sql-put-values-of-a-column-in-one-row — miracle173, Jun 04 '17 at 12:45
@RanchiRhino - It seems you can **not** cope "with any version" - you just indicated under a SQL Server reply that you are not familiar with some of its functions. And conversely, I believe LISTAGG is Oracle-specific and wouldn't work in other database products. Just state what database you are using, it's an easy question. — , Jun 04 '17 at 13:58

0xdb · Accepted Answer · 2018-06-25T11:45:04.900

The easiest way on Oracle to achieve that is listagg. Try something like this:

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

Yet another solution on Oracle. I think it will be easy to port to some other 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

If your table is really very small, than you may define your specific function. This function can be easily ported to any 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

Or more generic function:

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

One more generic function with 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

score 2 · Answer 2 · edited Jun 04 '17 at 10:27

2

In SQL Server you can get it using STUFF and FOR XML

 select STUFF((
           SELECT ', ' + CONCAT(CAST(id as varchar(10)), ' - ', name)
           FROM mytable md
           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
 ;
 GO


 | (No column name)                        |
 | :-------------------------------------- |
 |  1001 - Jon, 1002 - Jacob, 1003 - Cayle |

dbfiddle here

edited Jun 04 '17 at 10:27

marc_s

732,580
175
1,330
1,459

answered Jun 04 '17 at 08:23

McNets

10,352
3
32
61

STUFF and FOR XML is alien concept to me – RanchiRhino Jun 04 '17 at 08:50
I hope it helps you. – McNets Jun 04 '17 at 08:57

score 0 · Answer 3 · answered Jun 05 '17 at 17:07

0

SELECT listagg(id||' ' || '-'||' ' || name,', ') within GROUP (ORDER BY id) con FROM table;

answered Jun 05 '17 at 17:07

praveen

75
1
2

How to fetch different records in same row in sql?

3 Answers3