View Single Post
  #5 (permalink)  
Old June 11th, 2008, 08:38 PM
Old Pedant Old Pedant is offline
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Well, to get literally what you asked for:
Code:
SELECT id, parentid, name AS path, name 
FROM demo 
WHERE parentid = 0
     UNION
SELECT D2.id, D2.parentid, D1.name + '/' + D2.name, D2.name
FROM demo AS D1, demo AS D2
WHERE D2.parentid = D1.id AND D1.parentid = 0
     UNION
SELECT D3.id, D3.parentid, D1.name + '/' + D2.name + '/' + D3.name, D3.name
FROM demo AS D1, demo AS D2, demo AS D3
WHERE D3.parentid = D2.id AND D2.parentid = D1.id AND D1.parentid = 0
ORDER BY id;
Here's what I mean by "path" and what I think you mean, as well:
Code:
SELECT id, parentid, RIGHT('0000' + CSTR(id),4) AS path, name 
FROM demo 
WHERE parentid = 0
    UNION
SELECT D2.id, D2.parentid,RIGHT('0000'+CSTR(D1.id),4)+':'+RIGHT('0000'+CSTR(D2.id),4), D2.name
FROM demo AS D1, demo AS D2
WHERE D2.parentid = D1.id AND D1.parentid = 0
    UNION 
SELECT D3.id, D3.parentid,RIGHT('0000'+CSTR(D1.id),4)+':'+RIGHT('0000'+CSTR(D2.id),4)+':'+RIGHT('0000'+CSTR(D3.id),4), D3.name
FROM demo AS D1, demo AS D2, demo AS D3
WHERE D3.parentid = D2.id AND D2.parentid = D1.id AND D1.parentid = 0
ORDER BY path;
That use of
    CSTR(...)
is Access, which is how I tested this. In SQL Server, you would change
    CSTR(...)
to
    CONVERT(VARCHAR(100),...)