1 2 3 Previous Next 32 Replies Latest reply: Feb 8, 2013 12:30 PM by 977032 RSS

    Loading the data

    977032
      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
          JustinCave
          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
            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
              JustinCave
              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
                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
                  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
                    JustinCave
                    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
                      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
                        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
                          JustinCave
                          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
                            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
                              JustinCave
                              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
                                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
                                  JustinCave
                                  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
                                    "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