sql server根据分隔符拆分数据为一列

drop table #aaa 

---根据分隔符拆分为多个表字段，注意PARSENAME只能拆分最多4个分隔符的字符串

select 
FTYKH,PARSENAME(REPLACE(FCAL, ',', '.'), 1) AS ksdm1, PARSENAME(REPLACE(FCAL, ',', '.'), 2) ksdm2
,PARSENAME(REPLACE(FCAL, ',', '.'), 3) AS ksdm3, PARSENAME(REPLACE(FCAL, ',', '.'), 4) as ksdm4
,PARSENAME(REPLACE(FCAL, ',', '.'), 5) AS ksdm5, PARSENAME(REPLACE(FCAL, ',', '.'), 6) as ksdm6,
FKSNAME
INTO #aaa
 from DIM_TWORKROOM

--使用unpivot进行列转行
select row_number() over(order by FTYKH desc) as id,FTYKH,ksdm,FKSNAME  
from #aaa unpivot( ksdm for course  in(ksdm1,ksdm2,ksdm3,ksdm4,ksdm5,ksdm6))a