CTE 中字符串拼接

with cte[(field1,field2,...)]
as
(
   --基础语句，必须有字段
   select field1,field2,... from tableName
   union all
   --递归语句，必须有字段
   select field1,field2,... from tableName a,cte b where a.fieldName=b.fieldName
)
select * from cte;--只能跟一条查询语句

with dept
as
(
   select DEPTNAME as DEPTNAME,DEPTID,PDEPTID,cast(DEPTNAME as nvarchar(max)) as name from DEPARTMENT where DEPARTMENT.DEPTNAME=‘信息中心‘
   union all 
   select a.DEPTNAME,a.DEPTID,a.PDEPTID,cast(a.DEPTNAME+‘\‘+b.name as nvarchar(max)) as name from DEPARTMENT a  inner join dept b on a.DEPTID=b.PDEPTID
)
select * from dept where DEPTNAME=‘信息中心‘;