1 2 3 Previous Next 32 Replies Latest reply: Jan 24, 2013 12:53 AM by 985143 RSS

    Concacenate Many Columns text into one Column

    893638
      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
          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
            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
              repeated Post

              Edited by: Oceaner on Sep 27, 2012 11:37 PM
              • 4. Re: Concacenate Many Columns text into one Column
                Oceaner
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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