Could you post some data as a UNION query or a CREATE TABLE and then some INSERTs... (Not millions, just enough to get an idea of what you're dealing with.) I'm thinking this might be most easily done by a Stored Procedure with a CTE, but it's hard to tell without seeing some data.

(Maybe the ADD is just damaging my brain!)

Pieter