--> 生成测试数据表:tb

IF NOT OBJECT_ID('[tb]') IS NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[parentid] INT,[categoryname] NVARCHAR(10))
INSERT [tb]
SELECT 1,0,'test1' UNION ALL
SELECT 2,0,'test2' UNION ALL
SELECT 3,1,'test1.1' UNION ALL
SELECT 4,2,'test2.1' UNION ALL
SELECT 5,3,'test1.1.1' UNION ALL
SELECT 6,1,'test1.2'
GO
--SELECT * FROM [tb]

-->SQL查询如下:
;WITH T AS
(
    SELECT *,CAST(ID AS VARBINARY(MAX)) AS px 
    FROM tb AS A
    WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.[parentid])
    UNION ALL 
    SELECT A.*,CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))  
    FROM tb AS A
        JOIN T AS B
           ON A.[parentid]=B.id
)
SELECT [id],[parentid],[categoryname] FROM T 
ORDER BY px
/*
id          parentid    categoryname
----------- ----------- ------------
1           0           test1
3           1           test1.1
5           3           test1.1.1
6           1           test1.2
2           0           test2
4           2           test2.1

BOM节点排序，看你的数据应该是这个意思，稍微修改即可

------解决方案--------------------
SQL code

--实例1:

--> 生成测试数据表:tb

 

IF NOT OBJECT_ID('[tb]') IS NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb]([id] INT,[code] NVARCHAR(10),[pid] INT,[name] NVARCHAR(10))

INSERT [tb]

SELECT 1,'01',0,N'服装' UNION ALL

SELECT 2,'01',1,N'男装' UNION ALL

SELECT 3,'01',2,N'西装' UNION ALL

SELECT 4,'01',3,N'全毛' UNION ALL

SELECT 5,'02',3,N'化纤' UNION ALL

SELECT 6,'02',2,N'休闲装' UNION ALL

SELECT 7,'02',1,N'女装' UNION ALL

SELECT 8,'01',7,N'套装' UNION ALL

SELECT 9,'02',7,N'职业装' UNION ALL

SELECT 10,'03',7,N'休闲装' UNION ALL

SELECT 11,'04',7,N'西装' UNION ALL

SELECT 12,'01',11,N'全毛' UNION ALL

SELECT 13,'02',11,N'化纤' UNION ALL

SELECT 14,'05',7,N'休闲装'

GO

--SELECT * FROM [tb]

 

-->SQL查询如下:

 

;WITH T AS

(

    SELECT CAST(CODE AS VARCHAR(20)) AS CODE,*,

        CAST(ID AS VARBINARY(MAX)) AS px 

    FROM tb AS A

    WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.pid)

    UNION ALL 

    SELECT CAST(B.CODE+A.CODE AS VARCHAR(20)),A.*,

         CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))    

    FROM tb AS A

        JOIN T AS B

           ON A.pid=B.id

)

SELECT Code,Name FROM T 

ORDER BY px

/*

Code                 Name

-------------------- ----------

01                   服装

0101                 男装

010101               西装

01010101             全毛

01010102             化纤

010102               休闲装

0102                 女装

010201               套装

010202               职业装

010203               休闲装

010204               西装

01020401             全毛

01020402             化纤

010205               休闲装

 

(14 行受影响)

*/

 

--实例2:

--> 生成测试数据表:tb

IF NOT OBJECT_ID('[tb]') IS NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[parentid] INT,[categoryname] NVARCHAR(10))
INSERT [tb]
SELECT 1,0,'test1' UNION ALL
SELECT 2,0,'test2' UNION ALL
SELECT 3,1,'test1.1' UNION ALL
SELECT 4,2,'test2.1' UNION ALL
SELECT 5,3,'test1.1.1' UNION ALL
SELECT 6,1,'test1.2'
GO
--SELECT * FROM [tb]

-->SQL查询如下:
;WITH T AS
(
    SELECT *,CAST(ID AS VARBINARY(MAX)) AS px 
    FROM tb AS A
    WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.[parentid])
    UNION ALL 
    SELECT A.*,CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))  
    FROM tb AS A
        JOIN T AS B
           ON A.[parentid]=B.id
)
SELECT [id],[parentid],[categoryname] FROM T 
ORDER BY px
/*
id          parentid    categoryname
----------- ----------- ------------
1           0           test1
3           1           test1.1
5           3           test1.1.1
6           1           test1.2
2           0           test2
4           2           test2.1

(6 行受影响)
*/