SELECT
*
INTO
t
-
Load test data
From
(
SELECT
1
AS
ID,
'
Food
'
AS
Product,
0
AS
ParentID
Union
All
SELECT
2
,
'
Sport
'
,
0
Union
All
SELECT
3
,
'
Drink
'
,
1
Union
All
SELECT
4
,
'
Ball
'
,
2
Union
All
SELECT
5
,
'
Fruit
'
,
1
Union
All
SELECT
6
,
'
apple
'
,
5
Union
All
SELECT
Seduce
,
'
Banana
'
,
5
Union
All
SELECT
8
,
'
Football
'
,
4
Union
All
SELECT
9
,
'
Basketball
'
,
4
Union
All
SELECT
10
,
'
Peisi
'
,
3
Union
All
SELECT
11
,
'
Wohaha
'
,
3
)
AS
a
Go
WITH
Showtree (ID, product, orderby)
AS
(
SELECT
ID, PRODUCT, ID
From
t
WHERE
ParentID
=
0
-
Fixed member
Union
All
SELECT
T.ID, T.Product, Orderby
*
100
T.ID
From
Showtree
AS
s
Inner
Join
t
On
S.ID
=
T.ParentID)
SELECT
*
From
Showtree
ORDER
BY
CAST
(ORDERBY
AS
VARCHAR
(
50
))
-
Option (maxRecursion 1)
Drop
TABLE
t
