How to 'flatten' a self-referencing table?

[Guest]

Does anyone have SQL that can be used to 'flatten' a self-referencing table:
Table format:
ID
Data
ParID (refers to ID of another record)

I need this converted to a table with format:
Data1
Data2
Data3

Where the columns are the individal 'paths' of the tree, e.g.
1 Car 0
2 Buick 1
3 Chevy 1
4 Regal 2
5 Corvette 3
6 Malibu 3
Would be converted to:
Car, Buick, Regal
Car, Chevy, Corvette
Car, Chevy, Malibu

 

[Jason Lepack]

Using the following table structure, UDF, and Query

yourTable:
id - autonumber
itemname - text
parentId - number

' Recursive function that returns the itemized
' list for a leaf in the tree
Public Function flatten(id As long) As String
Dim rs As Recordset
Dim strSQL As String
Dim pid As long

strSQL = "SELECT itemname, parentid " & _
"FROM yourTable WHERE id = " & id

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

If rs!parentid = 0 Then
flatten = rs!itemname
Else
flatten = flatten(rs!parentid) & ", " & rs!itemname
End If

Set rs = Nothing
End Function

-- SQL for the query
SELECT flatten([id]) AS List
FROM yourtable
WHERE id Not In (select distinct parentid from yourtable)

Cheers,
Jason Lepack