Multiple records onto a single line?

[gruvn]

Hey there,

Not really access-specific, just need a hand with some SQL.

I have a database that has a key field (Tag), and for every occurrence of a particular value of "Tag", there are either 1 or 2 records. These records refer to 1) the initation or 2) the completion of a task.

As of right now, my data looks like this:

(SELECT TAG, START_DATE, START_V1, START_V2, END_DATE, END_V1, END_V2 FROM DATA)

TAG START_DATE START_V1 START_V2 END_DATE END_V1 END_V2
0007417 19990704 4306 6341 null null null
0007417 null null null 20010815 4415 6329
0007437 null null null 20020123 4320 6041

Is it possible to bring the end values (end_date, end_v1 and end_v2) date onto the same line as the start information, where applicable? I would like my table to look like this (using the data above) :

TAG START_DATE START_V1 START_V2 END_DATE END_V1 END_V2
0007417 19990704 4306 6341 20010815 4415 6329
0007437 null null null 20020123 4320 6041

I'm not sure if this can be done, since I'm essentially asking the query to overwrite the null values, which it views as data, and I view as garbage...

If this is impossible, or if someone can point me towards an appropriate technique, I'd be grateful.

Thanks!

 

[gruvn]

I'm using a different approach - thanks for looking!

 

[PHV]

A starting point:
SELECT TAG, Max(START_DATE), Max(START_V1), Max(START_V2), Max(END_DATE), Max(END_V1), Max(END_V2)
FROM DATA
GROUP BY TAG

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886