SQL 字符转行 用逗号分开的某字段转记录
有表tb, 如下:
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id value
----------- --------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
*/
--1. 旧的解决方法(sql server 2000)
create
table
tb(id
int
,value
varchar
(30))
insert
into
tb
values
(1,
'aa,bb'
)
insert
into
tb
values
(2,
'aaa,bbb,ccc'
)
go
--方法1.使用临时表完成
SELECT
TOP
8000 id = IDENTITY(
int
, 1, 1)
INTO
#
FROM
syscolumns a, syscolumns b
SELECT
A.id, value =
SUBSTRING
(A.[value], B.id, CHARINDEX(
','
, A.[value] +
','
, B.id) - B.id)
FROM
tb A, # B
WHERE
SUBSTRING
(
','
+ A.[value], B.id, 1) =
','
DROP
TABLE
#
--方法2.如果数据量小,可不使用临时表
select
a.id , value =
substring
(a.value , b.number , charindex(
','
, a.value +
','
, b.number) - b.number)
from
tb a
join
master..spt_values b
on
b.type=
'p'
and
b.number
between
1
and
len(a.value)
where
substring
(
','
+ a.value , b.number , 1) =
','
--2. 新的解决方法(sql server 2005)
create
table
tb(id
int
,value
varchar
(30))
insert
into
tb
values
(1,
'aa,bb'
)
insert
into
tb
values
(2,
'aaa,bbb,ccc'
)
go
--方法1.使用xml完成
SELECT
A.id, B.value
FROM
(
SELECT
id, [value] =
CONVERT
(xml,
'<root><v>'
+
REPLACE
([value],
','
,
'</v><v>'
) +
'</v></root>'
)
FROM
tb
) A
OUTER
APPLY
(
SELECT
value = N.v.value(
'.'
,
'varchar(100)'
)
FROM
A.[value].nodes(
'/root/v'
) N(v)
) B
--方法2.使用CTE完成
;
with
tt
as
(
select
id,[value]=
cast
(
left
([value],charindex(
','
,[value]+
','
)-1)
as
nvarchar(100)),Split=
cast
(stuff([value]+
','
,1,charindex(
','
,[value]+
','
),
''
)
as
nvarchar(100))
from
tb
union
all
select
id,[value]=
cast
(
left
(Split,charindex(
','
,Split)-1)
as
nvarchar(100)),Split=
cast
(stuff(Split,1,charindex(
','
,Split),
''
)
as
nvarchar(100))
from
tt
where
split>
''
)
select
id,[value]
from
tt
order
by
id
option
(MAXRECURSION 0)
DROP
TABLE
tb
/*
id value
----------- ------------------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
分类:
SQL SERVER
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了