all row values in one column

Question

I would like to display all values in one column. How may I do so?

Data looks like this:

-----------------------------------------------
| user_id | degree_fi | degree_en | degree_sv |
-----------------------------------------------
| 3601464 | 3700      |  1600     |  2200     |
|  1020   | 100       |  0        |   0       |
| 3600520 |  100      | 1300      |  1400     |
| 3600882 |  0        |   100     |  200      |
| 3600520 |  3200     |   800     |  600      |
| 3600520 |  400      | 3000      |  1500     |
-----------------------------------------------

What I would like to have is this:

-------------------------------------------------------------
| user_id | degree_fi    | degree_en       | degree_sv       |
--------------------------------------------------------------
| 3601464 | 3700         |  1600           |  2200           |
|  1020   | 100          |  0              |   0             |
| 3600520 | 100,3200,400 | 1300, 800, 3000 | 1400, 600, 1500 |
| 3600882 |  0           |   100           |  200            |
--------------------------------------------------------------

As you can see that the values of 3600520 are not only in one group but also in one column too. How may I do it?

Thanks in advance

create table USER_MULTI_DEGREE
(
  USER_ID   INTEGER not null,
  DEGREE_FI VARCHAR2(128),
  DEGREE_EN VARCHAR2(128),
  DEGREE_SV VARCHAR2(128)
);
insert into USER_MULTI_DEGREE (USER_ID, DEGREE_FI, DEGREE_EN, DEGREE_SV) values (3601464, '3700', '1600', '2200');
insert into USER_MULTI_DEGREE (USER_ID, DEGREE_FI, DEGREE_EN, DEGREE_SV) values (1020, '100', '0', '0');
insert into USER_MULTI_DEGREE (USER_ID, DEGREE_FI, DEGREE_EN, DEGREE_SV) values (3600520, '100', '1300', '1400');
insert into USER_MULTI_DEGREE (USER_ID, DEGREE_FI, DEGREE_EN, DEGREE_SV) values (3600882, '0', '100', '200');
insert into USER_MULTI_DEGREE (USER_ID, DEGREE_FI, DEGREE_EN, DEGREE_SV) values (3600520, '3200', '800', '600');
insert into USER_MULTI_DEGREE (USER_ID, DEGREE_FI, DEGREE_EN, DEGREE_SV) values (3600520, '400', '3000', '1500');

+1 for posting a complete SQL setup script (although it raises an error because DEGREE_ID can not be NULL ;-) ) — Frank Schmitt, Jul 18 '12 at 10:44

score 5 · Accepted Answer · answered Jul 18 '12 at 10:43

If you are using 11g R2, you can use the built-in listagg() function:

select user_id, listagg(degree_fi, ',') within group (order by degree_fi)
from user_Multi_degree
group by user_id

If you are using 11g R1, you'll have to define your own type for this - see AskTom: stragg function for an example.

score 1 · Answer 2 · answered Jul 18 '12 at 10:58

1

Since you are on 11g, and assuming it's 11gR2, you can use the built-in listagg() function; this is the analytic version for a slight variation on Frank Schmitt's answer:

select distinct user_id,
    listagg(degree_fi, ',') within group (order by degree_id)
        over (partition by user_id) as degree_fi,
    listagg(degree_en, ',') within group (order by degree_id)
        over (partition by user_id) as degree_en,
    listagg(degree_sv, ',') within group (order by degree_id)
        over (partition by user_id) as degree_sv
from user_multi_degree;

   USER_ID DEGREE_FI            DEGREE_EN            DEGREE_SV
---------- -------------------- -------------------- --------------------
      1020 100                  0                    0
   3600520 100,3200,400         1300,800,3000        1400,600,1500
   3600882 0                    100                  200
   3601464 3700                 1600                 2200

answered Jul 18 '12 at 10:58

Alex Poole

183,384
11
179
318

This is less efficient than Frank Schmitt's answer. – Rob van Wijk Jul 19 '12 at 07:29
@RobvanWijk - is that a general observation of analytic versus aggregate versions of functions, or something specific here? Or the `distinct` step? I've never compared the two properly. – Alex Poole Jul 19 '12 at 07:34
You can use analytic functions to do aggregations, but not vice versa. If you need to aggregate -here for example- and you use analytic functions, then those functions operate on a larger set, before the aggregation/filtering. Here, you have calculated the three listagg functions on 6 rows and after that you filter out the duplicates with the distinct. When you aggregate, the listagg aggregate function operates on just the four aggregated rows. – Rob van Wijk Jul 20 '12 at 11:01
@RobvanWijk - I see, I think; I guess I assumed the partition clause had a similar effect. That's given me something to think about. Thanks! – Alex Poole Jul 20 '12 at 14:24

OraNob · Answer 3 · 2012-07-18T14:10:57.353

0

Create and use an inline function for your SQL. Depending on the size of your table it may cause performance issues. If this is the case you might also consider using a materialized view.

Forgot - the new LISTAGG function.

listagg function in 11g release 2

edited Jul 18 '12 at 14:10

answered Jul 18 '12 at 10:34

OraNob

684
3
9

all row values in one column

3 Answers3

Linked