CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle11gr2
Geeta D
Geeta D

Reputation: 1

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

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   |
+-----+-----------+--------+---------------+

Upvotes: 0

Views: 55

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

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

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

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

Upvotes: 1

Related Questions