Possible pivot/CTE recursion restructuring of data

• DamianC

  SSCrazy Eights

  Points: 8006

  More actions

  September 7, 2015 at 3:52 am

  #304051

  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

• WayneS

  SSC Guru

  Points: 95405

  More actions

  September 7, 2015 at 9:48 am

  #1825003

  DamianC (9/7/2015)

  ---

  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.

  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

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• DamianC

  SSCrazy Eights

  Points: 8006

  More actions

  September 8, 2015 at 3:04 am

  #1825065

  Thanks Wayne

  Exactly what I needed

  - Damian