CODEWITHSUNDEEP
sqloracle-databaseplsql
Potato
Potato

Reputation: 172

How to update table with concatenation

I have table like this

create table aaa (id int not null, data varchar(50), numb int);

with data like this

begin 
  for i in 1..30 loop
    insert into aaa
    values (i, dbms_random.string('L',1),dbms_random.value(0,10));
  end loop;
end;

now im making this

select a.id, a.data, a.numb, 
count(*) over (partition by a.numb order by a.data) count, 
b.id, b.data,b.numb 
from aaa a, aaa b
where a.numb=b.numb
and a.data!=b.data
order by a.data;

and i want to update every row where those numbers are the same but with different letters, and in result i want to have new data with more than one letter (for example in data column- "a c d e"), i just want to create concatenation within. How can i make that? the point is to make something like group by for number but for that grouped column i would like to put additional value.

that is how it looks like in begining 

id | data |numb
1    q     1
2    z     8
3    i     7
4    a     2
5    q     4
6    h     1
7    b     9
8    u     9
9    s     4

That i would like to get at end

id | data |numb
1    q h   1
2    z     8
3    i     7
4    a     2
5    q s   4
7    b u   9

Upvotes: 0

Views: 65

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Try this

   SELECT MIN(id),
         LISTAGG(data,' ') WITHIN GROUP(
             ORDER BY data
        )  data,
        numb
 FROM aaa GROUP BY numb
 ORDER BY 1

Demo

Upvotes: 1

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

This selects 10 random strings 1 to 4 letters long, letters in words may repeat:

select level, dbms_random.string('l', dbms_random.value(1, 4)) 
  from dual connect by level <= 10

This selects 1 to 10 random strings 1 to 26 letters long, letters do not repeat and are sorted:

with aaa(id, data, numb) as (
    select level, dbms_random.string('L', 1), 
           round(dbms_random.value(0, 10))
      from dual connect by level <= 30)
select numb, listagg(data) within group (order by data) list
  from (select distinct data, numb from aaa)
  group by numb

Upvotes: 0

Related Questions