数据库中如何使用with函数做数据切割

如何将下面数据

列名

A,B

C

D

E

转换成

列名
A
B
C
D
E

简单写一下数据库代码实现 

MySQL、SQLITE：group_concat

Oracle： wm_concat

SQlServer：SELECT STUFF((SELECT ','+字段名 FROM 表名 for xml path('')),1,1,'') 

以SQL server为Demo 

先写了一个简单的未加withSQL，先将数据合并，数据切割成一个

select substring(value,1,charindex(',',value)-1),substring(value,charindex(',',value)+1,LEN(value)-charindex(',',value)) from
(SELECT
STUFF((SELECT ','+value  FROM 
    (
select 'A,B' value union select 'C' value union select 'D' value ) A  for xml path('')),1,1,'')  value
) A

在以上SQL的基础上，可加入with函数实现

with cte(value,name) as
(
SELECT  cast('' as varchar(100)) , cast(STUFF((SELECT ','+nn FROM (
select 'A,B' nn union select 'C' nn union select 'D' nn ) A  for xml path('')),1,1,'') as varchar(100))    
union all
select 
case when charindex(',',name)!=0
then 
cast(substring(name,1,charindex(',',name)-1) as varchar(100)) 
else name end,
case when charindex(',',name)!=0
then cast(substring(name,charindex(',',name)+1,LEN(name)-charindex(',',name)) as varchar(100))
else '' end   from cte where  name!=''
)
select value,name from cte

注意以上代码如果不加cast 函数 将报定位点类型和递归部分的类型不匹配

SQLITE 数据集跟以上不符合，可自行变化

WITH split(word, str) AS (
    SELECT '', (SELECT group_concat(part,';') FROM chq3g4)||';'
    UNION ALL SELECT
    substr(str, 0, instr(str, ';')),
    substr(str, instr(str, ';')+1)
    FROM split WHERE str!=''
)