Row To Column

[7106231]

Does anybody can help me
how to change row data to column data ....

for example i have like this :

date_stamp start_time tag_id value
---------- ---------- ------- -----
07-sep-01 00:00 A 1
07-sep-01 00:00 B 2
07-sep-01 00:00 C 3
07-sep-01 01:00 A 4
07-sep-01 01:00 B 5
07-sep-01 01:00 C 6

and i want to be like this

date_stamp tag_id 00:00 01:00
---------- ---------- ------- -----
07-sep-01 A 1 4
07-sep-01 B 2 5
07-sep-01 C 3 6


regards

yopy

 

[carp]

This is untested code, so bear with me. But I believe the following will work for you:

SELECT date_stamp, tag_id, sum(decode(start_time,'00:00',1,0) "00:00",
sum(decode(start_time,'01:00',1,0) "01:00"
FROM my_table
GROUP BY date_stamp, tag_id;

 

[manojg9]

u can try this :

select distinct date_stamp,b.tag_id,a.value as "00:00",c.value as "01:00" from
( select tag_id ,value
from tagg
where start_time = '00:00'
) a,tagg b,
( select tag_id ,value
from tagg
where start_time = '01:00'
) c
where a.tag_id = b.tag_id
and b.tag_id = c.tag_id

Or :
-------

declare
dt date;
val number;
tag char;
st_tim char(5);
cursor c1 is
select distinct date_stamp,tag_id
from tagg;
cursor c2 is
select distinct start_time
from tagg;
begin
open c2;
dbms_output.put('date_stamp tag ');
loop
fetch c2 into st_tim;
exit when c2%notfound;
dbms_output.put(st_tim);
dbms_output.put(' ');
end loop;
close c2;
dbms_output.put_line(' ');
open c1;
loop
fetch c1 into dt,tag;
exit when c1%notfound;
dbms_output.put(dt||' '||tag);
open c2;
loop
fetch c2 into st_tim;
exit when c2%notfound;
select value into val
from tagg
where date_stamp = dt
and tag_id = tag
and start_time = st_tim;
dbms_output.put(' ');
dbms_output.put(val);
dbms_output.put(' ');
end loop;
close c2;
dbms_output.put_line(' ');
end loop;
end;

 

[JimHorton]

Does Oracle support Pivot/Transform, as in Crosstab? This looks like a perfect fit.
--Jim