1 2 3 Previous Next 32 Replies Latest reply on Jan 24, 2013 6:53 AM by 985143 Go to original post
      • 15. Re: Concacenate Many Columns text into one Column
        sb92075
        we speak SQL.
        do you?

        post CREATE TABLE for your tables.
        post INSERT to provide us test data.

        show results that you desire from test data
        • 16. Re: Concacenate Many Columns text into one Column
          893638
          Yes I do use CMD and login to sqlplus... so I am familiar with it somewhat.
          But still a novice....

          My Table is called "Table1"
          "Table1" has Columns called "Del1" "Del2" etc. These columns include anything up to 255 characters of text.
          "Table1" also includes a blank Column called "Final Delivery".
          I want contents from "Del1" "Del2" Del3" etc... to end up as one piece of text in "Final Delivery".
          That's it.

          Sorry if I can't make this clear enough :/
          Thanks anyway.
          • 17. Re: Concacenate Many Columns text into one Column
            sb92075
            890635 wrote:
            Yes I do use CMD and login to sqlplus... so I am familiar with it somewhat.
            But still a novice....

            My Table is called "Table1"
            "Table1" has Columns called "Del1" "Del2" etc. These columns include anything up to 255 characters of text.
            "Table1" also includes a blank Column called "Final Delivery".
            I want contents from "Del1" "Del2" Del3" etc... to end up as one piece of text in "Final Delivery".
            That's it.

            Sorry if I can't make this clear enough :/
            Thanks anyway.
            above does not look like any SQL that I recognize.
            SQL> select 4000/255 from dual;
            
              4000/255
            ----------
            15.6862745
            Since VARCHAR2 limit is 4000, at most you could concatenate 15 columns of 255 into single column;
            which is a brain dead foolish implementation to even attempt.
            • 18. Re: Concacenate Many Columns text into one Column
              893638
              If I knew how to write the SQL to achieve what I want to achieve, I wouldn't be asking the question here.

              Brain dead foolish or not... I need that specific solution. so if you know the sql to achieve what I have described, please do as I don't need anything else.
              • 19. Re: Concacenate Many Columns text into one Column
                893638
                Purvesh K,

                I did what you suggested. ie. see below (but using my own table name and column names) and in sqlplus the message I get is "806 rows updated." But when I go to the "Concat_data" column in the table the values are all "Null". It hasn't concatenated anything.

                Any solution please?

                Your suggestion that I used:
                update test_table set concat_data = col1 || ',' || col2 || ',' || col3 || ',' || col4;
                • 20. Re: Concacenate Many Columns text into one Column
                  John Stegeman
                  If I understand what you are looking to do here, you should not be adding a column to your table, but creating a view. Here's an example:
                  create table x (a varchar2(255), b varchar2(255), c varchar2(255); -- this is your table
                  
                  -- assume there's some data in it :)
                  
                  create view y as
                  select a, b, c, a || ', ' || b || ', ' || c concat_column
                  from x;
                  use the view "y" instead of the table "x"

                  John

                  p.s. the likely reason you don't see the concatenated data in your form is that you forgot to commit. But, don't do this - use a view as I suggested
                  • 21. Re: Concacenate Many Columns text into one Column
                    Purvesh K
                    Sorry, I did not follow the question.

                    To answer a question asked previously "Why do we drop the tables and re-create them?". The reason for doing so is, if you are using the script to re-create a situation and find a solution, you might not be needing the tables. Hence, to facilitate the volunteers, I provided the DROP Table statement. It does not mean you have to do it. You are expected to grab the most important part of suggestion, which is to Concatenate the Columna and Update it, isn't it?
                    890635 wrote:
                    Purvesh K,

                    I did what you suggested. ie. see below (but using my own table name and column names) and in sqlplus the message I get is "806 rows updated." But when I go to the "Concat_data" column in the table the values are all "Null". It hasn't concatenated anything.

                    Any solution please?
                    Is it a situation that the Columns used for Concatenation are NULL? If they are all NULL, then the Concatenated Column shall also be set to NULL.

                    Now,
                    As you ask, the columns are being set to NULL:

                    I am understanding that below is the situation you are facing.
                    drop table test_table;       --> Avoid if you do not want the Statement
                     
                    create table test_table        ----> Necessary for people to replicate your Solution and ease the task of Creating Table Structures
                    (
                      col1      varchar2(2),
                      col2      varchar2(2),
                      col3      varchar2(2),
                      col4      varchar2(2)
                    );
                     
                     
                    insert into test_table values ('AA', 'BB', 'VD', 'E3');      ----> Also Important since we do not have access to your system. So a Little Sample Data, like this, can be helpful to understand the problem
                    insert into test_table values ('XA', 'EB', 'VF', 'E3');
                    insert into test_table values ('QG', 'B ', '$3', '4D');
                    insert into test_table values ('14', 'GT', '56', '%W');
                    insert into test_table values ('AA', 'BB', '', '');
                     
                    alter table test_table add concat_column varchar2(20);           -----> Alter the column to store the Concatenated Data. (Presuming you do not have any such Column.) 
                                                                                        ---------->If Column exists, then ignore this step.
                    
                    update test_table set concat_column = col1 || ',' || col2 || ',' || col3 || ',' || col4;     ---> Update the target (Concatenated Data) Column;
                    5 rows updated.
                    Now, if the above does not match your situation, then please use the above code to provide us with the Table Structure (as you desire to be or you can use the same structure for representation), Some Sample Data and the Expected Outcome from the Sample data.

                    It will also be appreciated if you could post your best effort; That enables us to help you from the point where you are stuck.

                    Please do not forget to post
                    select * from v$version;
                    Also, I have been repeatedly asking you to ask SQL related question on SQL and PL/SQL forum where right people can provide you excellent solutions.
                    I liked the solution by John, using a View.

                    Edited by: Purvesh K on Oct 3, 2012 4:18 PM
                    • 22. Re: Concacenate Many Columns text into one Column
                      893638
                      Hi Purvesh K
                      Thanks for the detailed explanation.

                      I copied this directly from my CMD:

                      SQL> update TBL_SPLIT_DELIVERABLES1 set CONCAT_DATA=DEL1||','||DEL2||','||DEL3||
                      *','||DEL4||','||DEL5||','||DEL6;*

                      *806 rows updated.*

                      From this you can see that my Table is called "TBL_SPLIT_DELIVERABLES1".
                      My columns that I want to concatenate are called "DEL1, DEL2, DEL3, DEL4, DEL5 & DEL6".
                      The column that I want the concatenated data to end up in is called "CONCAT_DATA".

                      When I hit enter, I get the message "806 rows updated"

                      But when I look at the table, there is nothing in the "CONCAT_DATA" column even though Columsn "DEL1, DEL2, DEL3, DEL4, DEL5 & DEL6" have (text) data in them.

                      Thanks again.
                      :|
                      • 23. Re: Concacenate Many Columns text into one Column
                        John Stegeman
                        You don't see the data because you didn't COMMIT

                        Still - DO NOT ADD ANOTHER COLUMN TO YOUR TABLE, USE A VIEW!
                        • 24. Re: Concacenate Many Columns text into one Column
                          893638
                          Hi John,

                          Thanks for your reply. I did this and it worked but only halfway. I only get data from the column titled "DEL1" into "Concat_data". The rest is still not there.

                          Here is a copy directly from my CMD:

                          SQL> create view y as
                          *2 select DEL1,DEL2,DEL3,DEL4,DEL5,DEL6,DEL1||','||DEL2||','||DEL3||','||DEL4|*
                          *|','||DEL5||','||DEL6 CONCAT_DATA*
                          *3 from TBL_SPLIT_DELIVERABLES1;*

                          View created.

                          What have I done wrong?
                          Thanks again :)
                          • 25. Re: Concacenate Many Columns text into one Column
                            Purvesh K
                            I earlier asked you to "Now, if the above does not match your situation, then please use the above code to provide us with the Table Structure (as you desire to be or you can use the same structure for representation), Some Sample Data and the Expected Outcome from the Sample data." and you did not follow.

                            Unless you provide us the information, we cannot help you.

                            See, the demonstration for suggestion by John.
                            create table test_table        ----> Necessary for people to replicate your Solution and ease the task of Creating Table Structures
                            (
                              col1      varchar2(2),
                              col2      varchar2(2),
                              col3      varchar2(2),
                              col4      varchar2(2)
                            );
                             
                             
                            insert into test_table values ('AA', 'BB', 'VD', 'E3');      ----> Also Important since we do not have access to your system. So a Little Sample Data, like this, can be helpful to understand the problem
                            insert into test_table values ('XA', 'EB', 'VF', 'E3');
                            insert into test_table values ('QG', 'B ', '$3', '4D');
                            insert into test_table values ('14', 'GT', '56', '%W');
                            insert into test_table values ('AA', 'BB', '', '');
                            
                            create or replace view vw_test_table as
                            select col1, col2, col3, col4, col1 || ',' || col2 || ',' || col3 || ',' || col4 concat_data
                              from test_table;
                              
                            select *
                              from vw_test_table;
                            
                            COL1 COL2 COL3 COL4 CONCAT_DATA 
                            ---- ---- ---- ---- ----------- 
                            AA   BB   VD   E3   AA,BB,VD,E3 
                            XA   EB   VF   E3   XA,EB,VF,E3 
                            QG   B    $3   4D   QG,B ,$3,4D 
                            14   GT   56   %W   14,GT,56,%W 
                            AA   BB             AA,BB,,
                            Compare this with your situation and see what different/incorrect have you done. Or, post exactly from your SQL Prompt (alongwith any Error).
                            • 26. Re: Concacenate Many Columns text into one Column
                              893638
                              Hi Purvesh K

                              My Table Structure is:

                              DEL1 varchar2(2),
                              DEL2 varchar2(2),
                              DEL3 varchar2(2),
                              DEL4 varchar2(2)


                              Sample Data is as such:

                              DEL1 DEL2 DEL3 DEL4 CONCAT_DATA
                              AA BB VD E3 AA,BB,VD,E3
                              XA EB VF E3 XA,EB,VF,E3
                              QG B $3 4D QG,B ,$3,4D
                              14 GT 56 %W 14,GT,56,%W
                              AA BB AA,BB,,

                              Edited by: 890635 on 4/10/2012 22:37
                              • 27. Re: Concacenate Many Columns text into one Column
                                893638
                                Purvesh,
                                I had no error.

                                My exact SQL is:

                                SQL> create view y as
                                2 select DEL1,DEL2,DEL3,DEL4,DEL5,DEL6,DEL1||','||DEL2||','||DEL3||','||DEL4|
                                |','||DEL5||','||DEL6 CONCAT_DATA
                                3 from TBL_SPLIT_DELIVERABLES1;

                                View created.


                                AS you can see, it says "View Created" but only the stuff from "DEL1, DEL2 and DEL3" are concatenated into "Concat_data".
                                "Del4,Del5 and Del6" don't make it.
                                Thanks
                                • 28. Re: Concacenate Many Columns text into one Column
                                  Purvesh K
                                  890635 wrote:
                                  AS you can see, it says "View Created" but only the stuff from "DEL1, DEL2 and DEL3" are concatenated into "Concat_data".
                                  "Del4,Del5 and Del6" don't make it.
                                  Thanks
                                  As in your earlier post, you mentioned the Table Structure contains DEL1, DEL2, DEL3, DEL4 columns; And your Create View contains DEL5, DEL6 as well; Where do the additional 2 (DEL5, DEL6) columns come from?
                                  One reason why DEL4, DEL5, DEL6 did not make thorough is because they are NULL.

                                  I must say you are not at all clear in specifying the requirements.

                                  For one last time I am asking you to, mention your requirements clearly and unambiguously.

                                  1. Post your Create Table Statement. (If my Create Table satisfies your need, then use if please.)
                                  2. Post the Sample data. (Use Mine if it suits you)
                                  3. Post the result from
                                  select * from your_table_name;
                                  4. Post the Create View statement
                                  5. Post the result from
                                  select * from your_view_name;
                                  If you post all the data correctly, I or other volunteers might be able to help you. If you do not, then I am going to ignore this thread.

                                  PS:-
                                  Which of the below, is more readable?

                                  SQL> create view y as
                                  2 select DEL1,DEL2,DEL3,DEL4,DEL5,DEL6,DEL1||','||DEL2||','||DEL3||','||DEL4|
                                  |','||DEL5||','||DEL6 CONCAT_DATA
                                  3 from TBL_SPLIT_DELIVERABLES1;

                                  OR
                                  SQL> create view y as
                                  2 select DEL1,DEL2,DEL3,DEL4,DEL5,DEL6,DEL1||','||DEL2||','||DEL3||','||DEL4|
                                  |','||DEL5||','||DEL6 CONCAT_DATA
                                  3 from TBL_SPLIT_DELIVERABLES1;
                                  Use
                                   (exactly the same) before and after your Code Sample, Create Table, Sample Data and Expected Output to present the requirement in a readable format.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                                  • 29. Re: Concacenate Many Columns text into one Column
                                    893638
                                    Hi Purvesh K
                                    Thanks for persevering with me. But I have already told you what my data is like and what I want out of it. Please tell me what doesn't make sense so I can make it clearer.
                                    I'll write it here as clearly as possible again.
                                     So I have a table called "TBL_SPLIT_DELIVERABLES1" 
                                     My Table structure is:
                                    
                                    DEL1 varchar2(2),
                                    DEL2 varchar2(2),
                                    DEL3 varchar2(2),
                                    DEL4 varchar2(2),
                                    DEL5 varchar2(2),
                                    DEL6 varchar2(2),
                                     For the sake of this exercise, my data is: 
                                    
                                    DEL1 DEL2 DEL3 DEL4 DEL5 DEL6 CONCAT_DATA 
                                    ---- ---- ---- ---- ---- ---- ----------- 
                                    AA   BB   VD   E3   BB   VD   AA,BB,VD,E3,BB,VD
                                    XA   EB   VF   E3   BB   VD   XA,EB,VF,E3,BB,VD 
                                    QG   B    $3   4D   BB   VD   QG,B ,$3,4D,BB,VD 
                                    14   GT   56   %W                    14,GT,56,%W 
                                    AA   BB                                    AA,BB,,
                                     To get the concat Data, I use this:
                                    
                                    SQL> create view y as
                                    2 select DEL1,DEL2,DEL3,DEL4,DEL5,DEL6,DEL1||','||DEL2||','||DEL3||','||DEL4||','||DEL5||','||DEL6 CONCAT_DATA
                                    3 from TBL_SPLIT_DELIVERABLES1;
                                     When I run the above code, the message I get is "View created." 
                                     But the result I get in the CONCAT_DATA is this:
                                    
                                    DEL1 DEL2 DEL3 DEL4 DEL5 DEL6 CONCAT_DATA 
                                    ---- ---- ---- ---- ---- ---- ----------- 
                                    AA   BB   VD   E3   BB   VD   AA,BB,VD
                                    XA   EB   VF   E3   BB   VD   XA,EB,VF 
                                    QG   B    $3   4D   BB   VD   QG,B ,$3 
                                    14   GT   56   %W                    14,GT,56 
                                    AA   BB                                    AA,BB,,
                                    As you can see, it does not concatenate all the data - but only the first few columns - even though there is data in the other columns.

                                    I hope this makes sense. I can't make it any clearer.
                                    Thanks again
                                    :)