Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Unanswered: Need help to build the query

    Below is one table's data:

    Emp_no E_Name S_Month Item_Code
    1 AA Jan P1,P2
    2 BB Jan P1
    3 CC Jan P6
    1 AA Feb P1
    2 BB Feb P5
    1 AA Mar P2,P6

    I have a requirement to display the data as below:

    Empno Ename Item_Code
    1 AA P1,P2,P6
    2 BB P1,P5
    3 CC P6

    It will be nice if any one can help.

    Thanks with Regards,

  2. #2
    Join Date
    Apr 2008
    Iasi, Romania
    Provided Answers: 2
    Your table is not in 3NF, so I do not think that is a fast SQL-based solution for you. You will have to write a SP.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Mar 2007
    According to the data model, the query would be more complex than it needed to be when data would be stored properly.

    The solution would consist from 2 steps:
    1) split the values in ITEM_CODE column using row generator; example is shown e.g. in this thread on OraFAQ:
    Another methods for achieving it may be found in these locations:
    2) Use any of string aggregation technique to concatenate its distinct values; the methods are described here:
    Just use the one that your Oracle version (it would be nice if you revealed it; the solution may dramatically differ according to it) supports.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts