September 7, 2015 at 3:52 am
Hello
I have a table (folderstructure) with the following columns:
pcmid, cmid, foldername
pcmid is the parent directory
cmid is the directory
foldername is the name of the directory
e.g. note, number of levels are unknown
cmid pcmid name
1 NULL c:\
101 1 level1
201 101 level2
45 101 level2a
56 201 level3
57 201 level3a
I'm looking to create a table that has cmid followed by the full directory path
So either (using above):
cmid path
1 c:\
101 c:\level1
201 c:\level1\level2
45 c:\level1\level2a
56 c:\level1\level2\level3
57 c:\level1\level2\level3a
etc.
OR
cmid 1 2 3 4
1 c:\
101 c:\ level1
201 c:\ level1 level2
45 c:\ level1 level2a
56 c:\ level1 level2 level3
57 c:\ level1 level2 level3a
etc.
I've can use recursion to allocate a level to each name /cmid/pcmid combination
At that point, i'm a little stuck
Appreciate, I could use multiple self joins
Is there a way this can be achieved using pivots or CTE recursion or something else
Thanks
Damian.
- Damian
September 7, 2015 at 9:48 am
DamianC (9/7/2015)
HelloI have a table (folderstructure) with the following columns:
pcmid, cmid, foldername
pcmid is the parent directory
cmid is the directory
foldername is the name of the directory
e.g. note, number of levels are unknown
cmid pcmid name
1 NULL c:101 1 level1
201 101 level2
45 101 level2a
56 201 level3
57 201 level3a
I'm looking to create a table that has cmid followed by the full directory path
So either (using above):
cmid path
1 c:101 c:\level1
201 c:\level1\level2
45 c:\level1\level2a
56 c:\level1\level2\level3
57 c:\level1\level2\level3a
etc.
OR
cmid 1 2 3 4
1 c:\
101 c:\ level1
201 c:\ level1 level2
45 c:\ level1 level2a
56 c:\ level1 level2 level3
57 c:\ level1 level2 level3a
etc.
I've can use recursion to allocate a level to each name /cmid/pcmid combination
At that point, i'm a little stuck
Appreciate, I could use multiple self joins
Is there a way this can be achieved using pivots or CTE recursion or something else
Thanks
Damian.
A recursive CTE is indeed one way to accomplish this.
It would have been helpful if you had posted the test data in a readily consumable format (where we could just copy/paste it). Since I had to do it for you, where can I send the bill? 😀
Actually, here is the test data in a readily consumable format:
DECLARE @TestData TABLE (
cmid INTEGER,
pcmid INTEGER,
name VARCHAR(256));
INSERT INTO @TestData (cmid, pcmid, name)
VALUES (1,NULL,'c:\'),
(101,1,'level1'),
(201,101,'level2'),
(45,101,'level2a'),
(56,201,'level3'),
(57,201,'level3a');
And the recursive cte to get this:
WITH cte AS
(
SELECT cmid,
pcmid,
CONVERT(VARCHAR(512), name) AS name
FROM @TestData t1
WHERE pcmid IS NULL
UNION ALL
SELECT t2.cmid,
t2.pcmid,
CONVERT(VARCHAR(512), cte.name + CASE WHEN RIGHT(cte.name,1) = '\' THEN '' ELSE '\' END + t2.name)
FROM @TestData t2
JOIN cte ON t2.pcmid = cte.cmid
)
SELECT *
FROM cte;
This produces the following results:
cmid pcmid name
----------- ----------- -------------------------
1 NULL c:101 1 c:\level1
201 101 c:\level1\level2
45 101 c:\level1\level2a
56 201 c:\level1\level2\level3
57 201 c:\level1\level2\level3a
Is this what you are looking for?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 8, 2015 at 3:04 am
Thanks Wayne
Exactly what I needed
- Damian
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply