hi!
im having a problem of how can i produce this. i've attached the sample data as the result of my query, now i created a table into which ill put some of those columns and also the other columns in the table will be computed. please check the attached for the data, there are types of chin_code, mostly there would only be 1 FRT, and 1 BSC ( i didnt have it in the sample), then there's TRKNO and sometimes TRKND, in my table basically some of the columns are

ORIGIN
DESTINATION
FRTAMT
BSCAMT......
TRUCKORIGINAMT
TRUCKDESTINATIONAMT


for example on the data i have 5 TRKNO and 1 FRT, when i put this on the table it should contain 6 row still but the value for the FRTAMT should be also put on the rows of TRKNO, i dont know how to display it, but heres my sample..

so we have 5 trkno so we have value on it

origin dest frtamt bscamt trucko truckdest

MNL DGT 0 0 600 0
MNL DGT 0 0 600 0
MNL DGT 0 0 600 0
MNL DGT 0 0 600 0
MNL DGT 0 0 600 0
MNL DGT 500 0 0 0

it should be transformed into

origin dest frtamt bscamt trucko truckdest

MNL DGT 500 0 600 0
MNL DGT 500 0 600 0
MNL DGT 500 0 600 0
MNL DGT 500 0 600 0
MNL DGT 500 0 600 0
MNL DGT 500 0 0 0

but if TRKNO is only 1 and 1 FRT so that would be two row in resultset, or if we also have TRKND as you can see we have value for truckdest, it should only be merged into one row

origin dest frtamt bscamt trucko truckdest

MNL DGT 500 0 0 0
MNL DGT 0 0 600 700

it should be displayed inputed to the table like this

origin dest frtamt bscamt trucko truckdest

MNL DGT 500 0 600 700

but if there is more than 1 TRKNO or TRKND, it should be compared to every TRKND or vice versa, for example

origin dest frtamt bscamt trucko truckdest

MNL DGT 0 0 600 0
MNL DGT 0 0 400 0
MNL DGT 0 0 0 300
MNL DGT 0 0 0 200
MNL DGT 0 100 0 0
MNL DGT 500 0 0 0

so we have 2 TRKNO, 2 TRKND and 1 FRT and 1 BSC, so it should be like this

origin dest frtamt bscamt trucko truckdest

MNL DGT 500 100 600 300
MNL DGT 500 100 600 200
MNL DGT 500 100 400 300
MNL DGT 500 100 400 200


thank you so much, i hope i've explained it well, on populating the values of the columns im thinking of using databse triggers on it, thanks again.



here's the result set of my query


" ","BRAN_CODE","CUST_CODE","COMM_CODE","ORIGIN","DESTINATION","COCL_CODE","COSI_CODE","COTY_CODE","FACTOR","BASIS","SEAR_CODE","SASH_SEQ_NO","CHIN_CODE"
"1","MNL","UR003-00000-12","SU014","MNL","DGT","A","20FTR","DV","1.3900","W","013","46103","TRKNO"
"2","MNL","UR003-00000-12","SU014","MNL","DGT","A","20FTR","DV","1.3900","W","1608","46103","TRKNO"
"3","MNL","UR003-00000-12","SU014","MNL","DGT","A","20FTR","DV","1.3900","W","1705","46103","TRKNO"
"4","MNL","UR003-00000-12","SU014","MNL","DGT","A","20FTR","DV","1.3900","W","1727","46103","TRKNO"
"5","MNL","UR003-00000-12","SU014","MNL","DGT","A","20FTR","DV","1.3900","W","305","46103","TRKNO"
"6","MNL","UR003-00000-12","SU014","MNL","DGT","A","20FTR","DV","1.3900","W","","46103","FRT"