all row values in one column
oracle, oracle11g, plsql, plsqldeveloper
Solution
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.
Problem
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'); ```