Sqlserver CTE 递归查询

CREATE TABLE [dbo].[T_GoodsType](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NOT NULL,
[LevelNum] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[SortNum] [int] NOT NULL,
[Status] [smallint] NOT NULL,
[CreateDate] [datetime] NOT NULL
) ON [PRIMARY]

insert t_goodsType 
values (0,1,'到店',1,1,getdate()),
(0,1,'发货',1,1,getdate()),
(0,1,'数字码',1,1,getdate()),
(0,1,'直充',1,1,getdate()),
(0,1,'虚拟卡券',1,1,getdate()),
(0,1,'红包',1,1,getdate())

insert t_goodsType 
values (1,2,'套餐类',1,1,getdate()),
(1,2,'代金券',1,1,getdate()),
(2,2,'同城配送',1,1,getdate()),
(2,2,'快递',1,1,getdate()),
(3,2,'数字码',1,1,getdate()),
(4,2,'视频会员',1,1,getdate()),
(4,2,'音频会员',1,1,getdate()),
(5,2,'视频会员',1,1,getdate()),
(5,2,'音频会员',1,1,getdate())



WITH CTE_Type AS 
(
    SELECT ID,ParentID,Name,LevelNum,CONVERT(nvarchar,Name) as Names,Status FROM T_GoodsType WHERE ParentID=0
    UNION ALL 
    SELECT A.ID,A.ParentID,A.Name,A.LevelNum,CONVERT(nvarchar,CTE_Type.Name+'-'+A.Name) Names,A.Status FROM T_GoodsType A 
    INNER JOIN CTE_Type ON A.ParentID=CTE_Type.ID
)
SELECT  * FROM CTE_Type