达梦 数据库 以逗号分割的列如何按逗号拆分 实现列转行

Question

C1,C2,C3,C4

就像这样以逗号分割的列，如何按逗号进行拆分，转为和原行ID对应的多行。

Answer 1

需要一个辅助的序列表 series，里面是自然数 1-n

select a.key_col, 
		cast(substring(',' + t1.input_string + ',', 
					max(s1.seq + 1), (s2.seq - max(s1.seq + 1))) as integer) as col_split,
		count(s2.seq) as place
from inputstrings as t1, series as s1, series as s2
where substring(',' + t1.input_string + ',', s1.seq, 1) = ','
and   substring(',' + t1.input_string + ',', s2.seq, 1) = ','
and s1.seq < s2.seq
and s1.seq <= DATALENGTH(t1.input_string) + 1
group by t1.key_col, t1.input_string, s2.seq
order by key_col, place