stack data into a single column

Question

SELECT * would result in:

course_id + group
-----------------
   1      + SE123
   1      + BA134
   2      + SE213
   3      + SE223
   3      + BA344
   4      + SE222

I would like it be like this:

course_id + group
---------------------
   1      + SE123/BA134
   2      + SE213
   3      + SE223/BA344
   4      + SE222

How can I do that?

Answer 1

For Oracle 11.2+ you can do this with LISTAGG. Here is a demonstration.

Setup:

DROP TABLE t1;
CREATE TABLE t1 (Course_Id Number(1), Group_Id Varchar2(20));

INSERT INTO t1 VALUES (1,'SE123');
INSERT INTO t1 VALUES (1,'BA134');
INSERT INTO t1 VALUES (2,'SE213');
INSERT INTO t1 VALUES (3,'SE223');
INSERT INTO t1 VALUES (3,'BA344');
INSERT INTO t1 VALUES (4,'SE222');

Query:

SELECT Course_ID, LISTAGG(Group_ID, '/') WITHIN GROUP (ORDER BY Group_ID DESC) 
FROM t1 GROUP BY Course_ID;

Output:

COURSE_ID GROUPS             
--------- --------------------
        1 SE123/BA134          
        2 SE213                
        3 SE223/BA344          
        4 SE222                

Here are some other string aggregation techniques for Oracle if you aren't on 11.2 yet.

Answer 2

Can you please be more specific on what you try to achieve?

I would be glad to help you out.

But as you subject says, I think you want to put all your data into one column. For this you can either go for the below mentioned methods:

1. concat( string1, string2 )

Here you can just concatenate two strings.(concat is Oracle built-in function )

1. SELECT 'Name is ' || first_name || last_name FROM employees;

Here you can just concatenate the columns using the (||)pipe operator.

Any comments would be appreciated.