Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    29

    Unanswered: Construct a query.

    Hi,

    I have a table tab_temp of the format

    FUNCTION_ID VARCHAR2(20),
    DAILY_TARGET NUMBER,
    DAILY_RESULT NUMBER,
    DAILY_VARIANCE NUMBER,
    WEEK1_TARGET NUMBER,
    WEEK1_RESULT NUMBER,
    WEEK1_VARIANCE NUMBER,
    WEEK2_TARGET NUMBER,
    WEEK2_RESULT NUMBER,
    WEEK2_VARIANCE NUMBER,
    WEEK3_TARGET NUMBER,
    WEEK3_RESULT NUMBER,
    WEEK3_VARIANCE NUMBER

    No I want to fetch records in such a way that I display target first, and then result and then vairance.
    e.g
    1st record
    function_id,daily_target,week1_target,week_2_targe t,week3_target
    2nd record
    function_id,daily_result,week1_result,week_2_resul t,week3_result
    3rd record
    function_id, daily_variance, week1_variance, week2_variance, week3_variance.

    So one function_id should have 3 sets of records.
    Now there could be several such function_ids.

    How should I construct a query, (This is a requirement )which would fetch the records in above defined format.

    Many thanks in advance.
    Ash

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select function_id
         , 1 as line_number
         , daily_target
         , week1_target
         , week2_target
         , week3_target
      from tab_temp 
    union all
    select function_id
         , 2
         , daily_result
         , week1_result
         , week2_result
         , week3_result
      from tab_temp 
    union all
    select function_id
         , 3
         , daily_variance
         , week1_variance
         , week2_variance
         , week3_variance
      from tab_temp 
    order
        by function_id
         , line_number
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Posts
    29
    I think this might work.

    Many Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •