Ahhh...nice. I guess the ids=ids wouldn't be needed since they are already determined. Dunno though, I'm likeing the 'Coalesce'. 😉 Good call!

I think I got it...

Select Coalesce(a.ryear,b.ryear,c.ryear) as rYear, a.rvalue as 'ValueOfSubID1',

b.rvalue as 'ValueOfSubID2', c.rvalue as 'ValueOfSubID3'

FROM

(Select * From MyTable WHERE ID = 1 and SubID = 2) a

FULL...

Yes that does work in that case. But try this add the row ----

(1992 1 3 23)

to Varcy's data.

Then the...

That is exactly what I was doing except I used an ISNULL in place of the Coalesce ,which I never knew existed. Thank you! Where I got lost was...

Hmmmm....this one is tougher than it looks. I was trying to use derived tables, but it gets ugly after you start needing more than two column sets. Ya...