Forum Stats

  • 3,854,952 Users
  • 2,264,439 Discussions
  • 7,905,846 Comments

Discussions

I have query or need a function and how it works. Two records into a single record.

user13034857
user13034857 Member Posts: 68 Blue Ribbon
edited Jul 3, 2014 4:28PM in SQL & PL/SQL

I am having following issue to work.

I better give my sample table That will expain al clearly.This is how I will have the table .

EMpIDField2CodeCode_text
100a3abc
100a7zxy
100a(null)(null)
200z(null)(null)
200x(null)(null)

I need to convert to as follows

EMpIDField2o/p_CodeO/p_Code_text
100a3-7abc-zxy
100a(null)(null)
200z(null)(null)
200x(null)(null)

If I have same value for Pk keys (EmpId,Field2)) and if code and code_text are different than i need to make it as a single record as per EmpId 100. If not all the records to be moved to target table

Please help me.

Thanks in Advance.

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,506 Red Diamond
    edited Jul 3, 2014 3:08PM

    Hi,

    user13034857 wrote:
    
    I am having following issue to work.
    
    I better give my sample table That will expain al clearly.This is how I will have the table .
    
    
    
    EMpIDField2CodeCode_text
    
    
    
    100
    a
    3
    abc
    
    
    100
    a
    7
    zxy
    
    
    100
    a
    (null)
    (null)
    
    
    200
    z
    (null)
    (null)
    
    
    200
    x
    (null)
    (null)
    
    
    
    
    
    
    
    
    
    
    
    I need to convert to as follows
    
    
    
    
    EMpIDField2o/p_CodeO/p_Code_text
    
    
    
    100
    a
    3-7
    abc-zxy
    
    
    100
    a
    (null)
    (null)
    
    
    200
    z
    (null)
    (null)
    
    
    200
    x
    (null)
    (null)
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    If I have same value for Pk keys (EmpId,Field2)) and if code and code_text are different than i need to make it as a single record as per EmpId 100. If not all the records to be moved to target table
    
    Please help me.
    
    Thanks in Advance.
     

    This does what you requested:

    SELECT    empid
    ,         field2
    ,         LISTAGG (code,      '-') WITHIN GROUP (ORDER BY code)  AS o_p_code
    ,         LISTAGG (code_text, '-') WITHIN GROUP (ORDER BY code)  AS o_p_code_text
    FROM      sample
    GROUP BY  empid
    ,         field2
    ,         DECODE (code, code_text, 1, 2)
    ;
    

    I hope this answers your question.

    If not, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.

    If you're asking about a DML statement, such as INSERT, the sample data will be the contents of the table(s) before the DML, and the results will be state of the changed table(s) when everything is finished.

    Explain, using specific examples, how you get those results from that data.

    Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

    See the forum FAQ: https://forums.oracle.com/message/9362002#9362002

  • shoblock
    shoblock Member Posts: 19

    not sure why you chose the decode like that?  true, it will work for the data provided, since decode will treat the null comparison as wanted, but what if CODE was "4" and CODE_TEXT was also "4"?

    how about: DECODE (code, NULL, 1, 2)

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,506 Red Diamond
    edited Jul 3, 2014 4:29PM

    Hi,

    shoblock wrote:
    
    not sure why you chose the decode like that?  true, it will work for the data provided, since decode will treat the null comparison as wanted, but what if CODE was "4" and CODE_TEXT was also "4"?
    
    how about: DECODE (code, NULL, 1, 2)
     

    OP said

    ...  if code and code_text are different than i need to make it as a single record ...
     

    OP did not explain the desired results when code and code_text were the same.  The query I posted should produce the results OP posted (I say "should" beacuase, without any usable sample data, I can't actually test it).  The situation you mention may be impossible in OP's table.  If that situation is possible, then you and I can only guess at what OP wants.  (For all we know, OP wants what the query above produces.)  Guessing isn't always a very good way to spend our time.

This discussion has been closed.