combining two colums in one column

Question

I have a very unusual task, and the more I think the more I prone to go postal.

Data looks like this:

-----------------------------------------------------------------------------------------
| user_id | prof_org_id | prof_org_fi | prof_org_pos_fi | prof_org_en | prof_org_pos_en |
 ----------------------------------------------------------------------------------------
| 3600520 |   70000     |1100         |  as             |  1600       |   ad            |
|  1020   |   80000     |  0          |                 |     0       |                 |  
| 3600520 |   90000     | 2500        | bs              |  1700       |   bd            |
| 3600624 |   100000    |100          |   d             |  1002       |    c            |
-----------------------------------------------------------------------------------------

Now what I would like to have is this:

------------------------------------------------
| user_id | prof_org_fi     | prof_org_en      |
 -----------------------------------------------
| 3600520 | 1100,as;2500,bs | 1600,ad;1700,bd | 
|  1020   |    0,           |   0,             |  
| 3600624 |  100,d          |  1002,c          |
------------------------------------------------

I assume that it requires some function, no? Thanks in advance

create table USER_MULTI_PROF_ORGS
(
  USER_ID              INTEGER not null,
  PROF_ORG_ID          INTEGER not null,
  PROF_ORG_FI          VARCHAR2(128),
  PROF_ORG_POS_FI      VARCHAR2(4000),
  PROF_ORG_EN          VARCHAR2(128),
  PROF_ORG_POS_EN      VARCHAR2(4000),
);

insert into USER_MULTI_PROF_ORGS (USER_ID, PROF_ORG_ID, PROF_ORG_FI, PROF_ORG_POS_FI, PROF_ORG_EN, PROF_ORG_POS_EN)
values (3600520, 70000, '1100', 'ad', '1600', 'ad');
insert into USER_MULTI_PROF_ORGS (USER_ID, PROF_ORG_ID, PROF_ORG_FI, PROF_ORG_POS_FI, PROF_ORG_EN, PROF_ORG_POS_EN)
values (1020, 80000, '0', null, '0', null);
insert into USER_MULTI_PROF_ORGS (USER_ID, PROF_ORG_ID, PROF_ORG_FI, PROF_ORG_POS_FI, PROF_ORG_EN, PROF_ORG_POS_EN)
values (3600520, 90000, '2500', 'bs', '1700', 'bd');
insert into USER_MULTI_PROF_ORGS (USER_ID, PROF_ORG_ID, PROF_ORG_FI, PROF_ORG_POS_FI, PROF_ORG_EN, PROF_ORG_POS_EN)
values (3600624, 100000, '100', 'd', '1002', 'c');

Isn't this pretty much the same as [yesterday's question](http://stackoverflow.com/q/11538816/266304)? You just need to concatenate the two columns together before doing the `listagg`. You really need to show some effort, you won't learn anything if someone gives you the solution and you don't try to understand and adapt it. — Alex Poole, Jul 19 '12 at 07:11

score 0 · Answer 1 · answered Jul 19 '12 at 08:16

0

If you are using Oracle 11g R2 have a look at the LISTAGG function.

answered Jul 19 '12 at 08:16

OraNob

684
3
9

1

Yes, but that part was covered in yesterday's question, as noted in my earlier comment. – Alex Poole Jul 19 '12 at 09:39

combining two colums in one column

1 Answers1