数据库脚本：

USE [NetWingCRM]

GO

/****** 对象:  Table [dbo].[Co_ItemNameSet]    脚本日期: 01/05/2015 14:13:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Co_ItemNameSet](

[ItemId] [int] IDENTITY(1,1) NOT NULL,

[ParentItemId] [int] NULL,

[ItemName] [varchar](50) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

查询语句：

with depInfo as (

 /* cast 转换类型 */

select cast(1 as varchar(20)) path,0 as depth, * from Co_ItemNameSet where ParentItemId=0

union all

select cast(b.path+'.'+cast(row_number() over (order by a.itemid) as varchar(10)) as varchar(20)),depth+1, a.* from Co_ItemNameSet a,depInfo b where a.parentitemid=b.itemid

)

select path,depth,itemid,parentitemid,itemname,SPACE(depth*2)+'|--'+itemname from depInfo  order by depth