I have a table here, named "Foo". The data is set up something like this.

ID     TableReference     DataId0     DataId1     DataId2
--     --------------     -------     -------     -------
 1     Prize                   3           4           5
 2     Prize                   4           5        NULL
 3     Cash                    1        NULL        NULL
 4     Prize                   8        NULL          12
 5     Foo                     2           3        NULL
 6     Cash                    8           1          10
 7     Foo                     5           1           2

Etc. The data is horribly set up, I know, but I didn't set it up that way. :) I'm only dealing with the after effect.

I'm trying to come up with a way to essentially "flatten" the table; that is, to display all the data to a point where the table "Foo" does not reference itself. I'm trying to figure out a sql query that I can do to get there.

Usually when I deal with recursion, I have (or can establish) parent IDs and set it up that way, but for this table there are seemingly multiple child and parent IDs creating a web-like structure instead of a hierarchy. So I'm at a loss where to even begin to write a sql query for something like this.

Note: There is no infinite looping (where one Foo points to another Foo, which points back to the original Foo) from what I've found.

Using t-sql. Thanks for any assistance, if at all possible.