It was a bit of a headache but my colleague came up with materialized path design pattern:
WITH
ParentChildRels
(ParentID, ChildID, KeyField, [Path], HierarchyLevel, Doc)
AS
(
SELECT
ParentID, ChildID, KeyField, CAST('root/' + Folder AS nvarchar(255)) AS Path, 1 AS HierarchyLevel, Doc
FROM
(
SELECT
dbo.Folder.ParentID, dbo.Folder.ID AS ChildID, dbo.Folder.Folder, dbo.[Document].DocumentID AS KeyField, dbo.[Document].[Document] AS Doc
FROM
dbo.[Document]
RIGHT OUTER JOIN
dbo.Folder
ON
dbo.[Document].FolderID = dbo.Folder.ID
) AS x
WHERE
(ParentID = 0)
UNION ALL
SELECT
r.ParentID, r.ChildID, r.KeyField, CAST(RTRIM(pr.Path) + '/' + r.Folder AS nvarchar(255)) AS Path, pr.HierarchyLevel + 1 AS HierarchyLevel, r.Doc
FROM
(
SELECT
Folder_1.ParentID, Folder_1.ID AS ChildID, Folder_1.Folder, Document_1.DocumentID AS KeyField, Document_1.[Document] AS Doc
FROM
dbo.[Document] AS Document_1
INNER JOIN
dbo.[Folder] AS Folder_1
ON
Document_1.FolderID = Folder_1.ID
) AS r
INNER JOIN
ParentChildRels AS pr
ON r.ParentID = pr.ChildID
)
SELECT DISTINCT TOP (100) PERCENT
ParentID, ChildID, KeyField, RTRIM(RTRIM([Path]) + '/' + Doc)
FROM
ParentChildRels AS ParentChildRels_1
WHERE Keyfield is not null
ORDER BY
RTRIM(RTRIM([Path]) + '/' + Doc), ParentID, ChildID, KeyField
I thought to share it with you all if you ever come into the same situation
In Word you can only store 2 bytes. That is why I use Writer.
modified on Monday, October 18, 2010 4:59 AM
