CODEWITHSUNDEEP
sqloracle-databaseoracle10gconcatenation
Ramie
Ramie

Reputation: 1201

Concatenate results from a SQL query in Oracle

I have data like this in a table

NAME PRICE
A    2
B    3
C    5
D    9
E    5

I want to display all the values in one row; for instance:

A,2|B,3|C,5|D,9|E,5|

How would I go about making a query that will give me a string like this in Oracle? I don't need it to be programmed into something; I just want a way to get that line to appear in the results so I can copy it over and paste it in a word document.

My Oracle version is 10.2.0.5.

Upvotes: 20

Views: 51915

Answers (7)

Kyle
Kyle

Reputation: 2685

I know this is a little late but try this: 

SELECT LISTAGG(CONCAT(CONCAT(NAME,','),PRICE),'|') WITHIN GROUP (ORDER BY NAME) AS CONCATDATA
FROM your_table

Upvotes: 17

Nick Krasnov
Nick Krasnov

Reputation: 27251

Here is another approach, using model clause:

-- sample of data from your question
with t1(NAME1, PRICE) as(
   select 'A',    2 from dual union all
   select 'B',    3 from dual union all
   select 'C',    5 from dual union all
   select 'D',    9 from dual union all
   select 'E',    5 from dual
) -- the query
 select Res
  from (select name1
             , price
             , rn
             , res
         from t1
         model
         dimension by (row_number() over(order by name1) rn)
         measures (name1, price, cast(null as varchar2(101)) as res)
         (res[rn] order by rn desc = name1[cv()] || ',' || price[cv()] || '|' ||  res[cv() + 1])
       )
where rn = 1

Result:

RES
----------------------
A,2|B,3|C,5|D,9|E,5|

SQLFiddle Example

Upvotes: 2

Art
Art

Reputation: 5782

-- Oracle 10g --

SELECT deptno, WM_CONCAT(ename) AS employees
  FROM   scott.emp
GROUP BY deptno;

Output:
     10  CLARK,MILLER,KING
     20  SMITH,FORD,ADAMS,SCOTT,JONES
     30  ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

Upvotes: 14

bonCodigo
bonCodigo

Reputation: 14361

Managed to get till here using xmlagg: using oracle 11G from sql fiddle.

Data Table:

COL1    COL2    COL3
1       0       0
1       1       1
2       0       0
3       0       0
3       1       0


SELECT
    RTRIM(REPLACE(REPLACE(
      XMLAgg(XMLElement("x", col1,',', col2, col3)

ORDER BY col1), '<x>'), '</x>', '|')) AS COLS
  FROM ab
;

Results:

COLS
1,00| 3,00| 2,00| 1,11| 3,10|

* SQLFIDDLE DEMO

Upvotes: 1

beder
beder

Reputation: 1074

Usually when I need something like that quickly and I want to stay on SQL without using PL/SQL, I use something similar to the hack below:

select sys_connect_by_path(col, ', ') as concat
from
(
  select 'E' as col, 1 as seq from dual
  union
  select 'F', 2 from dual
  union
  select 'G', 3 from dual
)
where seq = 3
start with seq = 1
connect by prior seq+1 = seq

It's a hierarchical query which uses the "sys_connect_by_path" special function, which is designed to get the "path" from a parent to a child.

What we are doing is simulating that the record with seq=1 is the parent of the record with seq=2 and so fourth, and then getting the full path of the last child (in this case, record with seq = 3), which will effectively be a concatenation of all the "col" columns

Adapted to your case:

select sys_connect_by_path(to_clob(col), '|') as concat
from
(
  select name || ',' || price as col, rownum as seq, max(rownum) over (partition by 1) as max_seq
  from
  (
   /* Simulating your table */
    select 'A' as name, 2 as price from dual
    union
    select 'B' as name, 3 as price from dual
    union
    select 'C' as name, 5 as price from dual
    union
    select 'D' as name, 9 as price from dual
    union
    select 'E' as name, 5 as price from dual
  )
)
where seq = max_seq
start with seq = 1
connect by prior seq+1 = seq

Result is: |A,2|B,3|C,5|D,9|E,5

Upvotes: 3

Ben
Ben

Reputation: 52853

As you're in Oracle 10g you can't use the excellent listagg(). However, there are numerous other string aggregation techniques.

There's no particular need for all the complicated stuff. Assuming the following table

create table a ( NAME varchar2(1), PRICE number);
insert all
into a values ('A',    2)
into a values ('B',    3)
into a values ('C',    5)
into a values ('D',    9)
into a values ('E',    5)
select * from dual

The unsupported function wm_concat should be sufficient:

select replace(replace(wm_concat (name || '#' || price), ',', '|'), '#', ',')
  from a;

REPLACE(REPLACE(WM_CONCAT(NAME||'#'||PRICE),',','|'),'#',',')
--------------------------------------------------------------------------------
A,2|B,3|C,5|D,9|E,5

But, you could also alter Tom Kyte's stragg, also in the above link, to do it without the replace functions.

Upvotes: 2

Woot4Moo
Woot4Moo

Reputation: 24316

Something like the following, which is grossly inefficient and untested.

    create function foo returning varchar2  as  
    (    
        declare bar varchar2(8000) --arbitrary number
        CURSOR cur IS
        SELECT name,price  
        from my_table  
        LOOP

    FETCH cur INTO r;

    EXIT WHEN cur%NOTFOUND;

       bar:= r.name|| ',' ||r.price || '|'

  END LOOP;  
  dbms_output.put_line(bar);
       return bar
    )

Upvotes: 1

Related Questions