SQL Server tree-shaped query

With tree

As
(
SELECT Dwpid, dwid,1 as X2LEVEL,DWMC,
/*cast (DWMC as NVARCHAR (max)) x2name,*/
CAST (+dwid as NVARCHAR (max)) x2id
From CF_XZDW
WHERE dwpid = ' 0 '
UNION All
SELECT c.dwpid, C.dwid, Tree.x2level + 1,C.DWMC,
/*cast (REPLICATE ('-', X2level * 4) + C.DWMC as NVARCHAR (max)) x2name,*/
Tree.x2id + '-' + CAST (C.dwid as NVARCHAR (max)) x2id
From CF_XZDW C
INNER JOIN Tree
On c.dwpid = Tree.dwid
)
SELECT DWMC, Dwid, dwpid from tree
ORDER by X2id;

Reference:

Http://blog.chinaunix.net/uid-451-id-3344447.html

SQL Server tree-shaped query