2 Replies Latest reply: Aug 26, 2014 10:08 AM by Michael Novo RSS

    return row value as column

    Michael Novo

      Greetings!  I currently have a query let's say

       

      SELECT Value, Period FROM MAS_CFUS_KEYACM_CONTROLDATA ORDER BY cf_keyword_ID

       

      RESULTS

      Value        Period

      --------------------------

      100.0002   01-JAN-14

      -35.68       01-DEC-14

      -1943.67   01-NOV-14

      678.0013  01-OCT-14

       

      This continues for about 15 results per cf_keyword_ID. 

       

      I'm looking to return the first VALUE in a new column called PREV1, the second value in PREV2, the third in PREV3 and so on.  The same thing for the dates.  And to do all of this for just the first 10 values of each cf_keyword_ID.  Can anyone suggest the best way to accomplish this?

       

      Thank you!!

        • 1. Re: return row value as column
          Frank Kulash

          Hi,

           

          That's called Pivoting.  Since I don't have a test copy of your table, I'll use scott.emp to illustrate.

          This shows the first 3 employees for each job, in order by hiredate, along with their hiredates:

          WITH    relevant_data    AS

          (

              SELECT  ename, job, hiredate

              ,       row_number () OVER ( PARTITION BY  job

                                           ORDER BY      hiredate

                                         )   AS r_num

              FROM    scott.emp

          )

          SELECT    *

          FROM      relevant_data

          PIVOT     (   MIN (ename)     AS ename

                    ,   MIN (hiredate)  AS hiredate

                    FOR r_num  IN (1, 2, 3)

                    )

          ORDER BY  job

          ;

          Output:

           

          JOB       1_ENAME    1_HIREDATE  2_ENAME    2_HIREDATE  3_ENAME    3_HIREDATE

          --------- ---------- ----------- ---------- ----------- ---------- -----------

          ANALYST   FORD       03-Dec-1981 SCOTT      19-Apr-1987

          CLERK     SMITH      17-Dec-1980 JAMES      03-Dec-1981 MILLER     23-Jan-1982

          MANAGER   JONES      02-Apr-1981 BLAKE      01-May-1981 CLARK      09-Jun-1981

          PRESIDENT KING       17-Nov-1981

          SALESMAN  ALLEN      20-Feb-1981 WARD       22-Feb-1981 TURNER     08-Sep-1981

          There are actually more than 3 CLERKS and SALESMEN.  That doesn't cause an error; only the 3 earliest ones are shown.

          As you can see, having fewer than 3 doesn't cause any errors, either.

           

          For more about pivots, see the Forum FAQ:

          Re: 4. How do I convert rows to columns?

           

           

          I hope this answers your question.

          If not, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.

          Explain, using specific examples, how you get those results from that data.

          If yod rather post a problem using scott.emp, then you don't need to post any sample data; just the results and explanation.

          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

          See the forum FAQ:

          Re: 2. How do I ask a question on the forums?

          • 2. Re: return row value as column
            Michael Novo

            That did the trick!  Thanks much!  I will make a note of how to post the inquiries better for next time.