oracle - 一列中的所有行值

Question

我想在一列中显示所有值。我该怎么做？

数据看起来像这样:

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

我想要的是:

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

如您所见，3600520 的值不仅在一组中，而且也在一列中。我该怎么做？

提前致谢

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');

Answer 1

如果您使用的是 11g R2，则可以使用内置的 listagg() 函数:

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

如果您使用的是 11g R1，则必须为此定义自己的类型 - 请参阅 AskTom: stragg function举个例子。