Posted by Erland Sommarskog on 11/02/05 00:50

Ben (vanevery@gmail.com) writes:
> Then I would want to display it in the following order:
> A,B,D,C,E.
>
> That would visually be:
> A
> B
> D
> C
> E
> Rather than:
> A
> B
> C
> D
> E
>
> I have tried to construct some way to join the two tables (#tmp and
> Folders) together to achieve this result, but I have been unable to
> come up with anything so far, besides my original post.

OK. Here is a modified version of my original script.

CREATE TABLE Folders (
folderID int NOT NULL PRIMARY KEY,
parentFolderID int NULL REFERENCES Folders(folderID),
name varchar(32) NOT NULL
)
go
INSERT Folders (folderID, parentFolderID, name)
SELECT 1, NULL, 'C:\'
UNION
SELECT 2, 1, 'Windows'
UNION
SELECT 3, 1, 'Program Files'
UNION
SELECT 4, 2, 'System 32'
UNION
SELECT 5, 4, 'Drivers'
go
CREATE TABLE #tmp (
folderID int NOT NULL,
sortkey varchar(4000) NOT NULL,
lvl tinyint NOT NULL
)
go
DECLARE @level tinyint
SELECT @level = 1
INSERT #tmp(folderID, sortkey, lvl)
SELECT folderID, str(folderID), 1
FROM Folders
WHERE parentFolderID IS NULL
WHILE 1 = 1
BEGIN
SELECT @level = @level + 1
INSERT #tmp(folderID, sortkey, lvl)
SELECT a.folderID, t.sortkey + str(a.folderID), @level
FROM Folders a
JOIN #tmp t ON t.folderID = a.parentFolderID
AND t.lvl = @level - 1
IF @@rowcount = 0
BREAK
END

SELECT space((t.lvl - 1) *10) + f.name
FROM #tmp t
JOIN Folders f ON f.folderID = t.folderID
ORDER BY sortkey
go
DROP TABLE #tmp
DROP TABLE Folders





--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

• Next in forum: Re: Is there any REGEXP library for TSQL?
• Prev in forum: Re: host_name
• Thread view: Re: Rendering directory structures

[Reply to this message]