hierarchical data from self referencing table in tree form

It looks like this has been asked an开发者_JAVA百科d 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.

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.

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