1 2 3 Previous Next 32 Replies Latest reply: Feb 8, 2013 12:30 PM by 977032 Go to original post RSS
      • 15. Re: Loading the data
        JustinCave
        That is not possible.

        Assuming by "record" you mean a row in the result set, you can't have rows with 10 columns and rows with 30 columns in the same result set.

        Justin
        • 16. Re: Loading the data
          977032
          So you saying if the source data is , BDF has same values upto some point plus it has extra

          CIF Whole 74 DAY
          CIF REVIEW
          CIF Gate way
          CIF Ball Park
          CIF Ball game
          CIF nobody
          CIF every one

          BDF Whole 74 DAY
          BDF REVIEW
          BDF Gate way
          BDF Ball Park
          BDF Ball game
          BDF every one
          BDF Some body
          BDF Whole FOODS
          BDF Ratings
          BDF people So you are saying


          CIF can not be captured with all 7 values as columns As first row
          BDF can not be captured with all 10 values as columns as Second row ?
          • 17. Re: Loading the data
            JustinCave
            If you want a single result set with two rows given that data, both rows would need to have 11 columns. You could, of course, populate the last 3 columns of the CIF row with values of NULL. But those 3 columns would have to exist if you wanted them to exist for the BDF row.

            Justin
            • 18. Re: Loading the data
              977032
              Yes in real target table there 53 columns to accomadate all values, -- There will be no 54 value one row may have only 10 , other may have 30 , third may have 40 to max of 53

              Thanks--Dileep
              • 19. Re: Loading the data
                JustinCave
                I'm not sure that I follow.

                Are you now saying that you don't want to do a dynamic pivot? Are you saying that you now want to do a static pivot where the result set will always have 53 columns (meaning that there can be, at most, 52 rows in the table for each COLUMN1 value)?

                Justin
                • 20. Re: Loading the data
                  977032
                  1) Table is created with 53 columns

                  2) 1st set row may only 10 columns data
                  3) 2nd Set of row may have 30 columns of data
                  4) 3rd set row may have only 5 columns of data
                  5) 4th set row may have all 53 columns of data ( which will be maximum)

                  Please let me know, that will help
                  • 21. Re: Loading the data
                    Stew Ashton
                    You didn't say what version of Oracle you have, so I won't tell you what version of Oracle is needed for this code. Now we're even.
                    WITH DATA(K, V) AS (SELECT
                      'ICF','RESUB' FROM DUAL UNION ALL SELECT
                      'DDF','RESF' FROM DUAL UNION ALL SELECT
                      'ICF','RESUB PA' FROM DUAL UNION ALL SELECT
                      'DDF',NULL FROM DUAL UNION ALL SELECT
                      'ICF','RESUB PB' FROM DUAL UNION ALL SELECT
                      'DDF','RESUB PF' FROM DUAL UNION ALL SELECT
                      'ICF','901 RESPONSE' FROM DUAL UNION ALL SELECT
                      'DDF','_' FROM DUAL UNION ALL SELECT
                      'ICF','901 APPEAl' FROM DUAL UNION ALL SELECT
                      'DDF','901 RES' FROM DUAL
                    )
                    select * from (
                      SELECT K, V, ROW_NUMBER() OVER(PARTITION BY K ORDER BY V) RN 
                      FROM DATA
                    )
                    pivot(max(v) col for rn in(1,2,3,4,5,6,7,8,9));
                    
                    K   1_COL        2_COL        3_COL        4_COL        5_COL        6_COL        7_COL        8_COL        9_COL      
                    --- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
                    DDF 901 RES      RESF         RESUB PF     _                                                                             
                    ICF 901 APPEAl   901 RESPONSE RESUB        RESUB PA     RESUB PB
                    To get more columns, just add ",10,11,12" and so on.
                    • 22. Re: Loading the data
                      JustinCave
                      I'm still having a great deal of difficulty understanding.

                      As we've discussed, a result set must have a fixed number of columns. Every row in the result set must have exactly the same number of columns. You cannot have a result set where one row has 10 columns and another has 30. That simply isn't possible in SQL. Just like all the rows in a table must have the same number of columns.
                      1) Table is created with 53 columns
                      I'm assuming that this table is the destination for your data and is different than the initial two-column table. If so, then it sounds like you want to do a static pivot rather than a dynamic pivot. Did you look through the link I gave you yesterday?

                      Justin
                      • 23. Re: Loading the data
                        977032
                        Justin,

                        1) I checked with DBAs, I was told it is 11g

                        2) if i run this sql

                        select * from (
                        SELECT ist column , 2nd column , ROW_NUMBER() OVER(PARTITION BY K ORDER BY 2nd column ) RN
                        FROM original table ----DATA
                        )
                        pivot(max( second column --v) col for rn in(1,2,3,4,5,6,7,8,9));


                        That should produce the result -- let me run and get back to you
                        • 24. Re: Loading the data
                          977032
                          Justin,


                          Thank you so much, I was just looking for that kind of format.

                          You made my day.

                          Your sql produced what I was looking for-- Thanks Again-- I appreciate
                          • 25. Re: Loading the data
                            977032
                            Justin,

                            Final question,

                            Since your query is producing exatly what I want -- can we insert the out put into target table or I should capture that to temp table ?-- -- if so what will be syntax ?

                            select * from (
                            SELECT K, V, ROW_NUMBER() OVER(PARTITION BY K ORDER BY V) RN
                            FROM DATA
                            )
                            pivot(max(v) col for rn in(1,2,3,4,5,6,7,8,9));

                            Thanks in advance
                            • 26. Re: Loading the data
                              Stew Ashton
                              I wasn't going to say anything, but actually that code is from me, not Justin.

                              If you want to insert the result into a table, just say
                              INSERT INTO OUT_TABLE (col1,col2,col2,...)
                              select * from (
                                SELECT K, V, ROW_NUMBER() OVER(PARTITION BY K ORDER BY V) RN 
                                FROM DATA
                              )
                              pivot(max(v) col for rn in(1,2,3,4,5,6,7,8,9));
                              Best regards,
                              Stew
                              P.S. First time someone confused me with an Oracle Ace! Thanks :)
                              • 27. Re: Loading the data
                                977032
                                Thank you so much Stew-- I appreciate

                                I hope I did not confuse you
                                • 28. Re: Loading the data
                                  JustinCave
                                  Stew Ashton wrote:
                                  P.S. First time someone confused me with an Oracle Ace! Thanks :)
                                  Given your other excellent posts, I suspect it is not the last time someone will make that mistake.

                                  Justin
                                  • 29. Re: Loading the data
                                    Stew Ashton
                                    Justin,

                                    I never would have expected to receive such a wonderful compliment. Thank you so much.

                                    Best regards,

                                    Stew