1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Concat fields

Discussion in 'SQL PL/SQL' started by dkoracle, Jun 27, 2012.

  1. dkoracle

    dkoracle Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    data:
    col1 col2 col3 col4 col5 col6
    123 456 789 567
    abc pqr
    xyz


    output:
    col1 col2 col3 col4 col5 col6
    123 456 789 567 123|456|789|567
    abc pqr abc|pqr
    xyz xyz


    can u help me on this.
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Please refer to the following links to know about few functions to change rows to columns.

    Convert rows to columns

    Convert rows to columns

    We can use pivoting concept or we can use listagg function to do so. There are few others which are explained in above navigations with few examples. Please go through them.
     
  3. dkoracle

    dkoracle Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    hi all,

    please find attached file for requeried output.

    regards,
    dkoracle
     

    Attached Files:

  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    I didn't get what you need exactly. Can you please elloborate the scenario what exactly you are expecting. You have 6 columns and data is only in 5 columns and in 6th column you need to concatenate those all 5 columns values and need to display right!
     
  5. dkoracle

    dkoracle Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    dear bharat,

    exactly right. if any column having null that column no need to concat.

    example: if col3 having null, should be concatinate col1 , col2,col4 and col5 with pipe symbol (col1|col2|col4|col5) .

    regards,
    dk
     
  6. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Do you need to just query it or while inserting you need to do it automatically for that column? If it is for just query just you can do like below:


    Code (Text):


    select e.*,

    case when empno is not null then empno||'|' end
    || case when ename is not null then ename||'|' end
    ||case when job is not null then job||'|' end
    ||case when mgr is not null then mgr||'|' end
    ||case when hiredate is not null then hiredate||'|' end
    ||case when sal is not null then sal||'|' end
    ||case when comm is not null then comm||'|' end
    ||case when deptno is not null then deptno||'|' end    "Result"
    from emp e;

     
    Output is shown in below document. Please refer to the document for result. As your requirement this query will pull the final record as concatenating all the values which are not null and then it displays to u. If you want to do it while inserting itself then try to use virtual columns concept and use this query there itself.
     

    Attached Files:

    dkoracle likes this.
  7. dkoracle

    dkoracle Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    thank you.
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    A solution that more closely matches the desired output:

    Code (SQL):
    SQL> CREATE TABLE mydata(
    2 col1 varchar2(10),
    3 col2 varchar2(10),
    4 col3 varchar2(10),
    5 col4 varchar2(10),
    6 col5 varchar2(10)
    7 );
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
    2 INTO mydata
    3 VALUES('123','456','789','567', NULL)
    4 INTO mydata
    5 VALUES('abc','pqr',NULL,NULL,NULL)
    6 INTO mydata
    7 VALUES('xyz',NULL,NULL,NULL,NULL)
    8 SELECT * FROM dual;
     
    3 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT col1, col2, col3, col4, col5,
    2 CASE WHEN col1 IS NOT NULL THEN col1 END ||
    3 CASE WHEN col2 IS NOT NULL THEN '|'||col2 END ||
    4 CASE WHEN col3 IS NOT NULL THEN '|'||col3 END ||
    5 CASE WHEN col4 IS NOT NULL THEN '|'||col4 END ||
    6 CASE WHEN col5 IS NOT NULL THEN '|'||col5 END col6
    7 FROM mydata;
     
    COL1        COL2        COL3          COL4        COL5        COL6
    ---------- ---------- ---------- ---------- ---------- --------------------------
    123          456          789          567                          123|456|789|567
    abc          pqr                                                        abc|pqr
    xyz                                                                       xyz
     
    SQL>
     
  9. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You need to have LTRIM to get '|' always between values. This can be removed by LTRIM

    Code (SQL):
    SQL> SELECT NULL col1,'456' col2,'789' col3,'567' col4, NULL col5 FROM dual
    UNION
    SELECT  'abc',NULL,'pqr',NULL,'stu' FROM dual
    UNION
    SELECT NULL,'xyz',NULL,NULL,NULL FROM dual  2    3    4    5  ;

    COL COL COL COL COL
    --- --- --- --- ---
    abc     pqr     stu
        456 789 567
        xyz

    SQL>
    SQL>
    SQL> WITH mydata AS (
      2  SELECT NULL col1,'456' col2,'789' col3,'567' col4, NULL col5 FROM dual
    UNION
      3    4  SELECT  'abc',NULL,'pqr',NULL,'stu' FROM dual
      5  UNION
      6  SELECT NULL,'xyz',NULL,NULL,NULL FROM dual
      7  )
      8  SELECT col1, col2, col3, col4, col5,
      9    CASE WHEN col1 IS NOT NULL THEN col1 END ||
     10    CASE WHEN col2 IS NOT NULL THEN '|'||col2 END ||
     11    CASE WHEN col3 IS NOT NULL THEN '|'||col3 END ||
     12    CASE WHEN col4 IS NOT NULL THEN '|'||col4 END ||
     13    CASE WHEN col5 IS NOT NULL THEN '|'||col5 END   col6
     14  FROM mydata ;

    COL COL COL COL COL COL6
    --- --- --- --- --- -------------------
    abc     pqr     stu abc|pqr|stu
        456 789 567     |456|789|567
        xyz             |xyz

    SQL>
    SQL>
    You can see the Col6 starts with Pipe here.

    Code (SQL):
    SQL>
    SQL> WITH mydata AS (
      2  SELECT NULL col1,'456' col2,'789' col3,'567' col4, NULL col5 FROM dual
      3  UNION
      4  SELECT  'abc',NULL,'pqr',NULL,'stu' FROM dual
      5  UNION
      6  SELECT NULL,'xyz',NULL,NULL,NULL FROM dual
      7  )
      8  SELECT col1, col2, col3, col4, col5,
      9    LTRIM ( CASE WHEN col1 IS NOT NULL THEN col1 END ||
      CASE WHEN col2 IS NOT NULL THEN '|'||col2 END ||
     10   11    CASE WHEN col3 IS NOT NULL THEN '|'||col3 END ||
     12    CASE WHEN col4 IS NOT NULL THEN '|'||col4 END ||
     13    CASE WHEN col5 IS NOT NULL THEN '|'||col5 END , '|')  col6
     14  FROM mydata ;

    COL COL COL COL COL COL6
    --- --- --- --- --- -------------------
    abc     pqr     stu abc|pqr|stu
        456 789 567     456|789|567
        xyz             xyz

    SQL>
     
    kiran.marla likes this.