1

I have 3 column in my test table like

ID | m_id    | S_m_id                                 
---------- --------------
1  |  1      |    1                  
1  |  2      |    3                  
1  |  3      |    4                  
1  |  5      |    6                  
2  |  1      |    1                  
2  |  2      |    3                  
2  |  3      |    4                  
3  |  5      |    6

I want to merge column 2 and 3 based on column 1 into one single row. And the result should be like

ID | merge_id                                 
---------- --------------
1  |  1,1;2,3;3,4;5,6                  
2  |  1,1;2,3;3,4                  
3  |  5,6
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
user1251973
  • 341
  • 3
  • 7
  • 16

1 Answers1

4

The function that you need is listagg() with does string concatenation when aggregating. You also need to first concatenate the ids together:

select id,
       listagg(m_id || ',' || s_m_id, ';') within group (order by m_id) as merge_ids
from test t
group by id;

By the way, the result data is incorrect (because the id is the same on all three rows). This probably accounts for the downvote.

EDIT (in response to comment):

You have two separators in the original example, one is comma (between ids) and one is a semicolon (between rows). You can replace either with '|' to get a pipe separator.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • hi gordon thanks...it worked...but how can i merge id column with separator |. – user1251973 Jun 17 '13 at 13:41
  • want to merge now 1 column and 2nd column with pipe separtor – user1251973 Jun 17 '13 at 13:44
  • select id,test.nextval listagg(m_id || ',' || s_m_id, ';') within group (order by m_id) as merge_ids from test t group by id; Using this query i m getting ora - 02287 sequence number not allowed here. – user1251973 Jun 17 '13 at 13:48