This discussion is archived
1 2 3 Previous Next 32 Replies Latest reply: Jan 23, 2013 10:53 PM by 985143 RSS

Concacenate Many Columns text into one Column

893638 Newbie
Currently Being Moderated
Hi,
I have many columns with text in it. I would like to ideally concatenate these column content into just ONE column.
Please help work out how to do this as I have no idea.
I am somewhat familiar with CMD so if you can help with this urgently that would be SO awesome.
Thanks
:)
  • 1. Re: Concacenate Many Columns text into one Column
    Purvesh K Guru
    Currently Being Moderated
    Do you want something this way?
    drop table test_table;
    drop table test_table1;
    
    create table test_table
    (
      col1      varchar2(2),
      col2      varchar2(2),
      col3      varchar2(2),
      col4      varchar2(2)
    );
    
    table test_table created.
    
    
    insert into test_table values ('AA', 'BB', 'VD', 'E3');
    1 rows inserted.
    
    create table test_table1 as select col1 || ',' || col2 || ',' || col3 || ',' || col4 as col from test_table;
    table test_table1 created.
    
    select *
      from test_table;
    COL1 COL2 COL3 COL4 
    ---- ---- ---- ---- 
    AA   BB   VD   E3
    
      
    select *
      from test_table1;
    COL         
    ----------- 
    AA,BB,VD,E3
    You should be posting this question at {forum:id=75}
  • 2. Re: Concacenate Many Columns text into one Column
    893638 Newbie
    Currently Being Moderated
    hi and thanks for the reply...

    i already have a table in oracle. It's called "Table1".
    The columns in this table are called "Delivery1", "Delivery2", "Delivery3", etc.

    How do I do this?
    Do I create a new blank Column first to put the concatenated data into it?
    If yes, then let's say this blank column is called "Final_Delivery".

    Then what do I do to make sure the concatenated data ends up in that column?

    Apologies in advance for my detailed question. But new to Oracle and need pretty much stepped instructions.

    Thanks
  • 3. Re: Concacenate Many Columns text into one Column
    Oceaner Explorer
    Currently Being Moderated
    repeated Post

    Edited by: Oceaner on Sep 27, 2012 11:37 PM
  • 4. Re: Concacenate Many Columns text into one Column
    Oceaner Explorer
    Currently Being Moderated
    890635 wrote:
    hi and thanks for the reply...

    i already have a table in oracle. It's called "Table1".
    The columns in this table are called "Delivery1", "Delivery2", "Delivery3", etc.

    How do I do this?
    Do I create a new blank Column first to put the concatenated data into it?
    If yes, then let's say this blank column is called "Final_Delivery".

    Then what do I do to make sure the concatenated data ends up in that column?

    Apologies in advance for my detailed question. But new to Oracle and need pretty much stepped instructions.

    Thanks
    You dont need to add a new column if you just want to display the data.
    select delivery1||delivery2||delivery3 "concat_data" from table1;
    Regards,
    Navneet
  • 5. Re: Concacenate Many Columns text into one Column
    Oceaner Explorer
    Currently Being Moderated
    890635 wrote:
    hi and thanks for the reply...

    i already have a table in oracle. It's called "Table1".
    The columns in this table are called "Delivery1", "Delivery2", "Delivery3", etc.

    How do I do this?
    Do I create a new blank Column first to put the concatenated data into it?
    If yes, then let's say this blank column is called "Final_Delivery".

    Then what do I do to make sure the concatenated data ends up in that column?

    Apologies in advance for my detailed question. But new to Oracle and need pretty much stepped instructions.

    Thanks
    You dont need to add a new column if you just want to display the data.
    select delivery1||delivery2||delivery3 "concat_data" from table1;
    Regards,
    Navneet
  • 6. Re: Concacenate Many Columns text into one Column
    Oceaner Explorer
    Currently Being Moderated
    890635 wrote:
    hi and thanks for the reply...

    i already have a table in oracle. It's called "Table1".
    The columns in this table are called "Delivery1", "Delivery2", "Delivery3", etc.

    How do I do this?
    Do I create a new blank Column first to put the concatenated data into it?
    If yes, then let's say this blank column is called "Final_Delivery".

    Then what do I do to make sure the concatenated data ends up in that column?

    Apologies in advance for my detailed question. But new to Oracle and need pretty much stepped instructions.

    Thanks
    You dont need to add a new column if you just want to display the data.
    select delivery1||delivery2||delivery3 "concat_data" from table1;
    Regards,
    Navneet
  • 7. Re: Concacenate Many Columns text into one Column
    Oceaner Explorer
    Currently Being Moderated
    890635 wrote:
    hi and thanks for the reply...

    i already have a table in oracle. It's called "Table1".
    The columns in this table are called "Delivery1", "Delivery2", "Delivery3", etc.

    How do I do this?
    Do I create a new blank Column first to put the concatenated data into it?
    If yes, then let's say this blank column is called "Final_Delivery".

    Then what do I do to make sure the concatenated data ends up in that column?

    Apologies in advance for my detailed question. But new to Oracle and need pretty much stepped instructions.

    Thanks
    You dont need to add a new column if you just want to display the data.
    select delivery1||delivery2||delivery3 "concat_data" from table1;
    Regards,
    Navneet
  • 8. Re: Concacenate Many Columns text into one Column
    893638 Newbie
    Currently Being Moderated
    Thanks again...

    So I want to display this data eventually on forms. So I think I do need to create a blank column.
    so let's say I want the concatenated data to end up in "Final_Delivery".
    How to do that?
    Thank you
  • 9. Re: Concacenate Many Columns text into one Column
    Ora Pro
    Currently Being Moderated
    If just want to concat the column's data then you can use concat function;
    Example:
    with t1 as
    (select 'ABC' column_1,'123' column_2,'PQR' column_3 from dual
    union all
    select '123' column_1,'XYZ' column_2,'879' column_3 from dual
    )
    select concat(column_1,concat(column_2,column_3)) concated_col from t1
    /
  • 10. Re: Concacenate Many Columns text into one Column
    893638 Newbie
    Currently Being Moderated
    I am using Oracle as the backend to an MS Access Front end database.
    So I will need to concatenate the data to a column and then from there use it in a form.

    For that reason, if I want to concatenate the data to a specific column ie. "Final_Delivery" how do I do that?

    Thanks :)
  • 11. Re: Concacenate Many Columns text into one Column
    Ora Pro
    Currently Being Moderated
    890635 wrote:
    Thanks again...

    So I want to display this data eventually on forms. So I think I do need to create a blank column.
    so let's say I want the concatenated data to end up in "Final_Delivery".
    What is "Final_Delivery" and which version of Oracle are you using?
    select * from v$version;
  • 12. Re: Concacenate Many Columns text into one Column
    Purvesh K Guru
    Currently Being Moderated
    This is doing what you intend to.
    drop table test_table;
    
    create table test_table
    (
      col1      varchar2(2),
      col2      varchar2(2),
      col3      varchar2(2),
      col4      varchar2(2)
    );
    
    insert into test_table values ('AA', 'BB', 'VD', 'E3');
    
    alter table test_table add concat_data varchar2(100);
    
    update test_table set concat_data = col1 || ',' || col2 || ',' || col3 || ',' || col4;
    
    select *
      from test_table;
    
    COL1 COL2 COL3 COL4 CONCAT_DATA                                                                                          
    ---- ---- ---- ---- ---------------------------------------------------------------------------------------------------- 
    AA   BB   VD   E3   AA,BB,VD,E3
    I would like to add that there is a Separate forum for SQL and PL/SQL, {forum:id=75} for similar topics and you have a higher probability of getting better responses there. Hence, if you have any further queries, please mark the question as answered (award Correct / Helpful points to the posts accordingly) and post a new question on SQL and PL/SQL forum.
  • 13. Re: Concacenate Many Columns text into one Column
    Oceaner Explorer
    Currently Being Moderated
    890635 wrote:
    Thanks again...

    So I want to display this data eventually on forms. So I think I do need to create a blank column.
    so let's say I want the concatenated data to end up in "Final_Delivery".
    How to do that?
    Thank you
    I dont have any idea about Forms...But just a guess that for form as well you must be writing some SQL, and you can use the sql there directly. Otherwise if you want to get it inserted into new column in existing table....First you have to add a new column to your table and then run update Statement to populate that data..similar to this
    SQL> select * from emp;
    
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO       TEST INFO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- ----------------------------------------
          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
          7566 JONES      MANAGER         7839 02-APR-81       2975                    20
          7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
          7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
          7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
          7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
          7839 KING       PRESIDENT            17-NOV-81       5000                    10
          7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
          7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
    
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO       TEST INFO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- ----------------------------------------
          7900 JAMES      CLERK           7698 03-DEC-81        950                    30
          7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
          7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
    
    14 rows selected.
    
    SQL> update emp set info=to_char(empno)||' '||ename||' '||job;
    
    14 rows updated.
    
    SQL> select * from emp;
    
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO       TEST INFO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- ----------------------------------------
          7369 SMITH      CLERK           7902 17-DEC-80        800                    20            7369 SMITH CLERK
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30            7499 ALLEN SALESMAN
          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30            7521 WARD SALESMAN
          7566 JONES      MANAGER         7839 02-APR-81       2975                    20            7566 JONES MANAGER
          7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30            7654 MARTIN SALESMAN
          7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30            7698 BLAKE MANAGER
          7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10            7782 CLARK MANAGER
          7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20            7788 SCOTT ANALYST
          7839 KING       PRESIDENT            17-NOV-81       5000                    10            7839 KING PRESIDENT
          7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30            7844 TURNER SALESMAN
          7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20            7876 ADAMS CLERK
    
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO       TEST INFO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- ----------------------------------------
          7900 JAMES      CLERK           7698 03-DEC-81        950                    30            7900 JAMES CLERK
          7902 FORD       ANALYST         7566 03-DEC-81       3000                    20            7902 FORD ANALYST
          7934 MILLER     CLERK           7782 23-JAN-82       1300                    10            7934 MILLER CLERK
    
    14 rows selected.
    
    SQL>
    Regards,
    Navneet
  • 14. Re: Concacenate Many Columns text into one Column
    893638 Newbie
    Currently Being Moderated
    First of all, thank you to everyone who replied. But I'm still not close to a solution that works :(

    Ora, Final Delivery is the name of the column I want to concatenate the data to.
    It needs to be concatenated to a spefic column because users should be able to make changes to the displayed data and add to it if they want to.

    Purvesh K, thank you for your input and in your Post you tell me "This is doing what you intend to." but I don't understand why am I dropping a table? and recreating it? As I said I already have a table and just need to concatenate the data from various columns in the table into just the ONE column which is called "Final Delivery".... so I don't understand why you say "drop table test_table; create table test_table, etc..."

    So in summary... I have a table with various columns of data. I want to concatenate all these columns of data into ONE column called Final Delivery. How do I concatenate this into the Final Delivery column?

    Thanks again
    :)
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points