This discussion is archived
1 2 3 Previous Next 32 Replies Latest reply: Feb 8, 2013 10:30 AM by 977032 RSS

Loading the data

977032 Newbie
Currently Being Moderated
Hello Guru's I have quick question to ask

If i have data like this

ICF RESUB
ICF RESUB PA
ICF RESUB PB
ICF 1001 RESPONSE
ICF 1001 APPEAL


ICF is in the first cloumn and ICF related values are in in second column

So my task is to create all into one row

ICF RESUB RESUB PA RESUB PB 901 RESPONSE 901 APPEAL REMS MOD ORD ( ICF is like parent only one time it should present)

I appreciate if same can be loaded with PL?SQL

Thanks In advance
  • 1. Re: Loading the data
    Justin Cave Oracle ACE
    Currently Being Moderated
    What version of Oracle are you using?

    Tim Hall has a nice page that discusses various string aggregation techniques. Personally, I prefer the user-defined aggregate function unless you're on 11.2 and can use LISTAGG.

    Justin
  • 2. Re: Loading the data
    977032 Newbie
    Currently Being Moderated
    Hello Justin thanks for your answer

    issue is to load the data as separate columns not as aggs

    colum1 | Column2 | Column 3 | column 4
    ICF RESUB RESUB PA RESUB PB

    I appreciate your response--Thanks
  • 3. Re: Loading the data
    Justin Cave Oracle ACE
    Currently Being Moderated
    How do you know which three values to use in the three columns you want? How do you know that you don't want the rows with data values of 1001 RESPONSE or ICF 1001 APPEAL to appear in the output?

    Justin
  • 4. Re: Loading the data
    Peter Gjelstrup Guru
    Currently Being Moderated
    user1584638 wrote:
    I have quick question to ask
    I quick answer then.

    You are probably talking about pivot then. Numerous techniques are found in the forum FAQ.

    My guess is that you may not a finite number of "IFC", and therefore you are looking for the dynamic variant (Warning: not going to be easy)

    Regards
    Peter
  • 5. Re: Loading the data
    977032 Newbie
    Currently Being Moderated
    Justin as i mentioned earlier

    Present table is like this


    Coulm1 column2

    ICF RESUB
    DDF RESF
    ICF RESUB PA
    DDF -----
    ICF RESUB PB
    DDF RESUB PF
    ICF 901 RESPONSE
    DDF ___
    ICF 901 APPEAl
    DDF 901 RES



    So my target table

    Coulm1 column2 Coulm2 column3 Coulm4 column5
    ICF RESUB RESUB PA RESUB PB 901 RESPONSE 901 APPEAl
    DDF RESF RESUB PF 901 RES


    You can see from above First has 5 values second one has only three values all in line
  • 6. Re: Loading the data
    Justin Cave Oracle ACE
    Currently Being Moderated
    OK, so you do want all 5 values to be in the output (in your last update, your expected output only had 4 columns not 6).

    Do you know that there will be at most 5 COLUMN2 values per COLUMN1 value (or some other fixed number)? If not, you'd need to look at Re: 4. How do I convert rows to columns?.

    Justin
  • 7. Re: Loading the data
    977032 Newbie
    Currently Being Moderated
    Probably it may be typo but to make it more clear


    Column 1 as shown earlier like ICF may be reapeated 10 times but each time it reapts it will have uniquie value in second column

    10 ICF rows will have 10 diiferent column2 values

    so final table should be


    Column1 Column1 Column1 Column1 Column1

    ICF A B C D and so on

    Sorry if i did not put it right way earlier
  • 8. Re: Loading the data
    977032 Newbie
    Currently Being Moderated
    Sorry

    so final table should be


    Column1 Column2 Column3 Column4 Column5

    ICF A B C D and so on

    Sorry if i did not put it right way earlier
  • 9. Re: Loading the data
    Justin Cave Oracle ACE
    Currently Being Moderated
    Are you saying that you know that a single COL1 value will appear at most 10 times (and thus that the result set will have exactly 11 columns)? Or are you saying that the COL1 value will appear an arbitrary and unknown number of times and so the number of columns in the result set will change?

    Assuming you are talking about the latter, you'd need to look at dynamic pivoting. There are multiple examples in the FAQ entry I linked to earlier that discuss how to do this.

    Justin
  • 10. Re: Loading the data
    977032 Newbie
    Currently Being Moderated
    I guess I have not made it clear


    Column 1 may apprear from 1 to 30 times in the present table. If it appreas 30 times in the present table means it has 30 values so final target table should be


    1 + 30

    ICF (1st Cloumn) + 30 values

    If DDF appreas only 10 times in present table

    target should be

    DDF ( 1st Column) + 10 values

    1 clomuns will be unique

    like

    ICF
    DDF
    MGF examples

    Please let me know I made it easy ?
  • 11. Re: Loading the data
    Justin Cave Oracle ACE
    Currently Being Moderated
    You want to pivot the data.

    It is still not clear to me whether you want a static pivot, where the number of columns is known at compile time, or whether you want a dynamic pivot, where the number of columns is variable and determined at runtime. My guess is that you are looking to do a dynamic pivot. That's why I linked to information on doing a dynamic pivot.

    Justin
  • 12. Re: Loading the data
    977032 Newbie
    Currently Being Moderated
    Yes it will be Dynamic because

    ICF may have 30 values in second coulmn

    BBF may have only 10 Values in second coulmn

    CDF may have 40 values in second coulm

    so final target should be

    1st Column
    ICF (First Row) + 30 columns
    BBF ( second row ) + 10 columns
    CDF ( third row) + 40 columns

    Thanks
  • 13. Re: Loading the data
    Justin Cave Oracle ACE
    Currently Being Moderated
    You know that a result set has to have the same number of columns for each row, right? Assuming you expect to return multiple rows (which you do in your third post but not in any of your other examples), the number of columns in the result set will have to be the same for each row.

    The FAQ entry that I linked to above walks through different approaches to doing a dynamic pivot. Have you gone through that link?

    Justin
  • 14. Re: Loading the data
    977032 Newbie
    Currently Being Moderated
    "result set has to have the same number of columns for each row, right? "


    If ICF has 30 rows in the Source table means

    ICF 1st column (First Row) + 30 columns it needs fill in target table

    Second record

    BBF has only 10 rows in source table

    BBF first column (SecondRow) + 10 Columns of data needs to fii in

    Third record

    MNF has only 20 records

    MNF first column (third row) + 20 columns needs to fillin


    Each first value will not have same number of columns to fill in the Target table
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points