Construct a query.

sashish4529 · #1 (**permalink**) 02-25-05, 02:57

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

r937 · #2 (**permalink**) 02-25-05, 08:52

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

sashish4529 · #3 (**permalink**) 02-27-05, 10:16

I think this might work.

Many Thanks.

Internet Services

Web Development

Digital Marketing

Consumer Tech