CODEWITHSUNDEEP
sqlsql-server
Beta033
Beta033

Reputation: 2013

hierarchical data from self referencing table in tree form

It looks like this has been asked and answered in all the simple cases, excluding the one that I'm having trouble with. I've tried using a recursive CTE to generate this; however maybe a cursor would be better? Or maybe a set of recursive functions will do the trick?

Can this be done in a cte?

consider the following table

PrimaryKey   ParentKey  
1            NULL       
2            1       
3            6
4            7
5            2
6            1
7            NULL

should yield

PK
1
-2
--5
-6
--3
7
-4

where the number of - marks equal the depth, my primary difficulty is the ordering.

Upvotes: 3

Views: 2147

Answers (2)

mdma
mdma

Reputation: 57717

Here's my offering - you also get the path to each node, if you want it. Change the str(x,4) call - make 4 the maximum length of the primary key when converted to decimal.

WITH TreePrinter(id, parent, path, prefix) AS
(
   SELECT 
      PrimaryKey, ParentKey, 
      CAST(str(PrimaryKey,4) AS varchar(max)),
      CAST('' AS varchar(max))
   FROM YourTable
   WHERE ParentKey IS NULL
   UNION ALL 
   SELECT child.PrimaryKey, child.ParentKey, 
     CAST(parent.path+'/'+STR(child.PrimaryKey,4) AS varchar(max)),
     CAST(parent.prefix+'-' AS varchar(max)),
     FROM YourTable parent
     INNER JOIN TreePrinter child ON child.id=parent.ParentKey
)
SELECT prefix+str(id) FROM TreePrinter
ORDER BY path

Upvotes: 1

Aaronaught
Aaronaught

Reputation: 122654

It's kind of kludgey to do with an adjacency list schema, but it can be done with a recursive CTE:

WITH Hierarchy AS
(
    SELECT
        PrimaryKey, ParentKey,
        CAST('/' + CAST(PrimaryKey AS varchar(10)) AS varchar(50)) AS [Path],
        CAST('' AS varchar(50)) AS Prefix
    FROM @Tbl
    WHERE ParentKey IS NULL

    UNION ALL

    SELECT
        t.PrimaryKey, t.ParentKey,
        CAST(h.[Path] + '/' + CAST(t.PrimaryKey AS varchar(10)) AS varchar(50)),
        CAST(h.Prefix + '-' AS varchar(50))
    FROM Hierarchy h
    INNER JOIN @Tbl t
        ON t.ParentKey = h.PrimaryKey
)
SELECT [Path], Prefix + CAST(PrimaryKey AS varchar(10)) AS Node
FROM Hierarchy
ORDER BY [Path]

To get the exact results you want, you need both the Prefix column (which does the "indentation") and the surrogate Path column, which actually does the ordering.

Upvotes: 8

Related Questions