Combining SQL rows in just one row (Oracle)

Question

I'm using an Oracle Database on PL/SQL Developer, and I want to combine the resulted rows of my query in just one row.

I have this:

   Column 1
1. Row 1
2. Row 2
3. Row 3

I want this:

   Column 1
1. Row 1, Row 2, Row 3

My query:

SELECT DISTINCT produto.ds_produto || ' Lote ->' || itmvto_estoque.cd_lote || 'Validade ->' || itmvto_estoque.dt_validade
FROM  itmvto_estoque, mvto_estoque, produto, atendime
WHERE itmvto_estoque.cd_mvto_estoque = mvto_estoque.cd_mvto_estoque
AND   produto.cd_produto = itmvto_estoque.cd_produto
AND   mvto_estoque.cd_atendimento = 1532174
AND   produto.cd_especie = 1

The database version is 10.

I found some similar questions here (like this one), but all of them are a little different, because I need to do it in just one column.

EDIT: The answer here for example used two columns and resulted more than one row.

you should try to make your sample query relevant to your sample data. Your query as posted is quite painful to read. — user2877959, Aug 18 '17 at 14:04
or https://stackoverflow.com/questions/42609917/listagg-alternative-in-oracle-10g — Klas Lindbäck, Aug 18 '17 at 14:20
Not exactly, the example used two columns and resulted more than one row with group by. I need one column and one row. — CrisCM, Aug 18 '17 at 14:35

score 2 · Accepted Answer · answered Aug 18 '17 at 14:14

2

If you don't have at least Oracle 11, you will not be able to use LISTAGG. Therefore, one alternative would be using XMLAGG, like this:

select 
rtrim(xmlagg(xmlelement(e, column1, ', ')).extract('//text()').getclobval(), ',')
from your_table

Note that this probably does not work if some form of group by is necessary, but it will help to get you started.

answered Aug 18 '17 at 14:14

Thomas Tschernich

1,264
15
29

Thanks. I don't use a group by, nevertheless I got no result with this: `SELECT DISTINCT rtrim(xmlagg(xmlelement(e, produto.ds_produto || ' Lote ->' || itmvto_estoque.cd_lote || 'Validade ->' || itmvto_estoque.dt_validade, ', ')).extract('//text()').getclobval(), ',') FROM itmvto_estoque, mvto_estoque, produto, atendime` – CrisCM Aug 18 '17 at 14:38
I wont be able to help you on that query if you do not provide table structure. – Thomas Tschernich Aug 18 '17 at 14:43

score 0 · Answer 2 · answered Aug 18 '17 at 14:43

As you probably know, the PIVOT function in PL /SQL did not appear until Oracle 11g.

The following article provides some options more succinctly than I could.

http://oraclecoder.com/tutorials/three-ways-to-transpose-rows-into-columns-in-oracle-sql--160

score 0 · Answer 3 · answered Aug 18 '17 at 15:11

Try this. I can't take credit though, I found it here: https://dba.stackexchange.com/questions/171997/how-to-obtain-the-functionality-of-listagg-function-in-oracle-10g

SQL> with tbl(col1) as (
     select 'Row 1' from dual union all
     select 'Row 2' from dual union all
     select 'Row 3' from dual
   )
   SELECT SUBSTR(SYS_CONNECT_BY_PATH (col1, ', '), 3) col1
         FROM (SELECT col1 , ROW_NUMBER () OVER (ORDER BY col1 ) rn,
                      COUNT (*) OVER () cnt
                 FROM tbl)
        WHERE rn = cnt
   START WITH rn = 1
   CONNECT BY rn = PRIOR rn + 1;

COL1
----------------------------------------------------------------------
Row 1, Row 2, Row 3

Combining SQL rows in just one row (Oracle)

3 Answers3