一列中的所有行值 [英] all row values in one column

问题描述

我想在一列中显示所有值.我该怎么办?

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

数据如下:

-----------------------------------------------
| 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的值不仅在一组中，而且也在一列中.我该怎么办?

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?

预先感谢

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

推荐答案

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

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 

如果您使用的是11g R1，则必须为此定义自己的类型-请参见

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

这篇关于一列中的所有行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！