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