一个字段内容有固定符号分列查询解决思路

一个字段内容有固定符号分列查询
姓名          能力类型
张三 “计划/组织/协调能力、沟通能力、分析判断能力、培养他人能力”
李四 “沟通能力、协调能力、学习能力"

想根据上面的内容查询出
姓名         能力1                       能力2            能力3              能力4
张三    计划/组织/协调能力   沟通能力   分析判断能力   培养他人能力
李四     沟通能力                    协调能力     学习能力

效果跟Excel中的固定符号分列功能一样效果。
------解决思路----------------------

with tt (name,ss) as (

select '张三','计划/组织/协调能力、沟通能力、分析判断能力、培养他人能力' union all

select '李四','沟通能力、协调能力、学习能力'

)

,t as(

select tt.name 姓名,ss+'、' 能力类型,LEN(ss+'、')-LEN(REPLACE(ss+'、','、','')) cnt from tt

)

,cte as 

(

select *

	,1 lvl

	,substring(t.能力类型,1,CHARINDEX('、',t.能力类型)-1)单独能力

	,substring(t.能力类型,CHARINDEX('、',t.能力类型)+1,4000) ss 

from t where CHARINDEX('、',t.能力类型)>0

union all

select t.*

	,cte.lvl+1

	,substring(cte.ss,1,CHARINDEX('、',cte.ss)-1)s

	,substring(cte.ss,CHARINDEX('、',cte.ss)+1,4000)

from t join cte on t.姓名=cte.姓名 and cte.lvl<t.cnt 

)

select  

	cte.姓名

	,max(case lvl when 1 then 单独能力 else '' end) 能力1

	,max(case lvl when 2 then 单独能力 else '' end) 能力2

	,max(case lvl when 3 then 单独能力 else '' end) 能力3

	,max(case lvl when 4 then 单独能力 else '' end) 能力4

	,max(case lvl when 5 then 单独能力 else '' end) 能力5

	--..........

from cte

group by cte.姓名

最后一步查询还是只能假定字段数，并不自动。看来只要是不确定分割个数的，必须用动态SQL才能完美实现。