Using Recursive SQL and XML trick to PIVOT(OK, concat) a "Document Folder Structure Relationship" table, works like MySQL GROUP_CONCAT

I'm in the process of building out a Data Warehouse and encountered this issue along the way.

In the environment, there is a table that stores all the folders with the individual level.  For example, if a document is created here:{App Path}\Level 1\Level 2\Level 3\{document}, then the DocumentFolder table would look like this:
Level 1
Level 2
Level 3

To my understanding, the table was built so that:
  • Each proposal can have multiple documents stored at various locations
  • Different users working on the proposal will have different access level to the folder; if one user is assigned access to a folder level, she/he can see all the sub folders and their content.

Now we understand from an application point of view why this table was built this way.  But you can quickly see the pain this causes the report writer to show a document link on the report.  I wasn't surprised to find the report query had 5 self outer joins, which is at the mercy of nobody creating a document that is buried 6 levels deep, and not to mention the degradation in performance.

With the help of 2 posts (at the end of this post), I was able to come up with this solution:

  1. Use recursive SQL to build out the folder path
  2. Use SQL XML trick to concat the strings.

Code (a reminder, I built this code in a stored procedure.  If you copy the syntax into a simple query window and execute, you'll get an incorrect syntax error)

-- Get all folders and group them by the original DocumentFolderID in PTSDocument table
WITH DocFoldersByDocFolderID(PTSDocumentFolderID_Original, PTSDocumentFolderID_Parent, sDocumentFolder, nLevel)
(-- first member
SELECT 'PTSDocumentFolderID_Original' = d1.PTSDocumentFolderID
, PTSDocumentFolderID_Parent
, 'sDocumentFolder' = sName
, 'nLevel' = CONVERT(INT, 1000000)
) AS d1
ON d1.PTSDocumentFolderID = df1.PTSDocumentFolderID
-- recursive
SELECT ddf1.PTSDocumentFolderID_Original
, df1.PTSDocumentFolderID_Parent
, 'sDocumentFolder' = df1.sName
, 'nLevel' = ddf1.nLevel - 1
FROM dbo.PTSDocumentFolder_DY AS df1 WITH(READPAST)
INNER JOIN DocFoldersByDocFolderID AS ddf1
ON df1.PTSDocumentFolderID = ddf1.PTSDocumentFolderID_Parent


-- Flatten out folder path

DocFolderSingleByDocFolderID(PTSDocumentFolderID_Original, sDocumentFolder)
(SELECT dfbdf.PTSDocumentFolderID_Original
, 'sDocumentFolder' = STUFF((SELECT '\' + sDocumentFolder
FROM DocFoldersByDocFolderID
WHERE (PTSDocumentFolderID_Original = dfbdf.PTSDocumentFolderID_Original)
ORDER BY PTSDocumentFolderID_Original, nLevel
FOR XML PATH ('')),1,1,'')
FROM DocFoldersByDocFolderID AS dfbdf
GROUP BY dfbdf.PTSDocumentFolderID_Original


And voila, I use the second CTE to join back to my original query (which is now a CTE for Source as we can now use MERGE to do INSERT and UPDATE at the same time).

Each part of this solution would not solve the problem by itself because:

  • If I don't use recursion, I cannot build out the path properly.  If I use the XML trick only, then I don't have the originating folder ID info that I need to link to the document.
  • If I don't use the XML trick, then I don't have one row per document to show in the report.
    I could conceivably do this in the report function, but I'd rather not deal with the beginning or ending backslash and how to attach the document name.
  • PIVOT doesn't do strings and UNPIVOT runs into the same problem as the above.
I'm excited that each version of SQL server provides us new tools to solve old problems and/or enables us to solve problems in a more elegant way

The 2 posts that helped me along:

Print | posted on Wednesday, April 4, 2012 5:29 PM


No comments posted yet.

Your comment:


Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski