Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
For which DBMS is this? SQL-Server, Oracle, Postgres, MySQL, other? –  ypercube Mar 12 '12 at 7:12
    
This is a very bad idea. It will kill performance and add a lot of difficulty to querying based on group. –  JNK Mar 12 '12 at 13:55

2 Answers 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.

share|improve this answer

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.

share|improve this answer
    
+1 for the link to ORACLE-BASE –  miracle173 Apr 14 '12 at 19:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.