This discussion is archived
1 2 3 Previous Next 32 Replies Latest reply: Feb 8, 2013 10:30 AM by 977032 Go to original post RSS
  • 15. Re: Loading the data
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you so much Stew-- I appreciate

    I hope I did not confuse you
  • 28. Re: Loading the data
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    Justin,

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

    Best regards,

    Stew

Legend

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