Combine multiple column values to one and print the one string in multiple rows - oracle SQL

Question

I am not knowing where to start with this query. Stuff () function is not helping to get my desired result. Any help is appreciated.

My table:

+-----+-----------+--------+
| uid |   uname   |  host  |
+-----+-----------+--------+
|   1 | testuser  | host 1 |
|   2 | testuser  | host 2 |
|   3 | testuser2 | host 3 |
|   4 | testuser2 | host 4 |
+-----+-----------+--------+

expected output:

+-----+-----------+--------+---------------+
| uid |   uname   |  host  | combined host |
+-----+-----------+--------+---------------+
|   1 | testuser  | host 1 | host1,host2   |
|   2 | testuser  | host 2 | host1,host2   |
|   3 | testuser2 | host 3 | host3,host4   |
|   4 | testuser2 | host 4 | host3,host4   |
+-----+-----------+--------+---------------+

probably this can help: https://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle — Leela Venkatesh K, Feb 07 '19 at 06:56

Zaynul Abadin Tuhin · Answer 1 · 2019-02-07T07:41:05.633

1

use LISTAGG and subquery join

    with cte as
(
select 1  uid1 ,'testuser' as uname,'host 1' as host from DUAL union all
select 2  uid1 ,'testuser' as uname,'host 2' as host from DUAL union all
select 3  uid1 ,'testuser2' as uname,'host 3' as host from DUAL union all
select 4  uid1 ,'testuser2' as uname,'host 4' as host from DUAL

)
  select cte.uname,cte.host,val from cte join (
 select uname,LISTAGG(host,',') within group (order by host) as val
 from cte group by uname) t on cte.uname=t.uname

dmeo link

UNAME       HOST          VAL
testuser    host 1  host 1,host 2
testuser    host 2  host 1,host 2
testuser2   host 3  host 3,host 4
testuser2   host 4  host 3,host 4

edited Feb 07 '19 at 07:41

answered Feb 07 '19 at 06:49

Zaynul Abadin Tuhin

31,407
5
33
63

This is the current error: ORA-01489: result of string concatenation is too long 01489. 00000 - "result of string concatenation is too long" *Cause: String concatenation result is more than the maximum size. – Geeta D Feb 07 '19 at 07:36
@GeetaD https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=e36ffad133f3762485cc6702cc5b140d check this link – Zaynul Abadin Tuhin Feb 07 '19 at 07:41

Barbaros Özhan · Answer 2 · 2019-02-07T07:49:51.807

You can use listagg() window analytic function as below :

with tab("uid",uname,host ) as
(
 select 1,'testuser' ,'host 1' from dual union all
 select 2,'testuser' ,'host 2' from dual union all
 select 3,'testuser2','host 3' from dual union all
 select 4,'testuser2','host 4' from dual
)
select  t2.*, t1."combined host"
  from
(select uname, listagg(host,',') within group (order by uname)
     as "combined host"          
   from tab
  group by uname ) t1
  inner join ( select * from tab ) t2 on t1.uname = t2.uname;

+-----+-----------+--------+---------------+
| uid |   UNAME   |  HOST  | combined host |
+-----+-----------+--------+---------------+
|   1 | testuser  | host 1 | host1,host2   |
|   2 | testuser  | host 2 | host1,host2   |
|   3 | testuser2 | host 3 | host3,host4   |
|   4 | testuser2 | host 4 | host3,host4   |
+-----+-----------+--------+---------------+

Demo

Combine multiple column values to one and print the one string in multiple rows - oracle SQL

2 Answers2