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
  • 30. Re: Loading the data
    977032 Newbie
    Currently Being Moderated
    Good Morning Stew / Jason,

    I have one more issue with the result set


    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));


    For V value it is bringing two records inspite using distinct (becuase source has type 1, typ2 value being same, description differs, we need only one value)

    101 APPEAL 101 APPEAL 101 ORD RESP 101 ORD RESP 101 ORDER 101 ORDER


    How do i supress the second value ?

    Appreciate your help
  • 31. Re: Loading the data
    Justin Cave Oracle ACE
    Currently Being Moderated
    For V value it is bringing two records inspite using distinct
    Sorry, where are you using DISTINCT? I don't see anywhere in the code that you posted that you are doing a DISTINCT.
    101 APPEAL 101 APPEAL 101 ORD RESP 101 ORD RESP 101 ORDER 101 ORDER
    I'm guessing that this is supposed to represent 6 rows in the base table. And I'm guessing that you want to reduce that to 3 rows and then pivot those rows. If so
    INSERT INTO OUT_TABLE (col1,col2,col2,...)
      select * from (
        SELECT K, V, ROW_NUMBER() OVER(PARTITION BY K ORDER BY V) RN 
          FROM (SELECT DISTINCT k, v 
                  FROM DATA)
      )
    pivot(max(v) col for rn in(1,2,3,4,5,6,7,8,9));
    If that is not what you want, please post the DDL to create your tables, the DML to populate them with sample data, and post the output that you expect.

    Justin
  • 32. Re: Loading the data
    977032 Newbie
    Currently Being Moderated
    Sorry Justin for delay in response.

    The Query filtered dups and data came out correct

    Thanks again for the help--Regards -



    Thanks Goes to Stew as well
1 2 3 Previous Next

Legend

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