1 2 3 Previous Next 32 Replies Latest reply: Feb 8, 2013 12:30 PM by 977032 Go to original post RSS
      • 30. Re: Loading the data
        977032
        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
          JustinCave
          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
            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