SQL server中如何按照某一字段中的分割符将记录拆成多条

[limitstar]

如下表：
CREATE TABLE [dbo].[t_checksite](
[StudyIdentity] [bigint] NOT NULL,
[CheckSite] [nvarchar](256) NULL
) ON [PRIMARY]

insert into t_checksite values (1,'{经胸超声心动图;}')
insert into t_checksite values (2,'{经胸超声心动图;甲状腺及颈部淋巴结;颈总动脉;}')
insert into t_checksite values (3,'{经胸超声心动图;甲状腺及颈部淋巴结;}')

如何通过将checksite字段按照分号拆开，将记录拆成多条，拆解后查询的结果如下：
StudyIdentity checksite
1 '{经胸超声心动图;}'
2 '{经胸超声心动图;}'
2 ‘{甲状腺及颈部淋巴结;}’
2 ‘{颈总动脉;}’
3 '{经胸超声心动图;}'
3 ‘{甲状腺及颈部淋巴结;}’

哪位大师可以帮忙拆解处理一下，万分感谢！

[Alexhohom]

为什么要在数据库查询里拆解呢？不能放在程序里面嘛？

[zjcxc]

SELECT A.[StudyIdentity], N'{' + T.c.value('.', 'nvarchar(max)') + N';}'
FROM [dbo].[t_checksite] A
	CROSS APPLY(SELECT CONVERT(xml,
		'<r><![CDATA['
		+ REPLACE(SUBSTRING([CheckSite], 2, LEN([CheckSite]) - 3), N';', N']]></r><r><![CDATA[')
		+ ']]></r>'
	)) B(c)
	CROSS APPLY B.c.nodes('/r/text()') T(c)

[yellowrace]

create function [dbo].[fn_split](
  @source varchar(max),
  @seperator varchar(8)
)
returns table
as
  return select target.item from( 
    select convert(xml,'<v>' + replace(@source, @seperator, '</v><v>') + '</v>') as text
  ) as source outer apply( 
    select n.v.value('.', 'varchar(256)') as item from source.text.nodes('/v') as n(v) 
  ) as target where target.item is not null;

select s.studyidentity,r.item as checksite from t_checksite as s
cross apply (
select * from dbo.fn_split(substring(s.checksite,2,len(s.checksite)-3),';')
) as r
--where len(r.item)>0

[yellowrace]

CSDN 的广告真够多的，到处都穿插

[吉普赛的歌]

------------------- 在tempdb库创建 测试表 BEGIN -----------------------
USE tempdb
GO
IF OBJECT_ID('[dbo].[t_checksite]') IS NOT NULL DROP TABLE [dbo].[t_checksite]
GO
CREATE TABLE [dbo].[t_checksite](
[StudyIdentity] [bigint] NOT NULL,
[CheckSite] [nvarchar](256) NULL
) ON [PRIMARY]
insert into t_checksite values (1,'{经胸超声心动图;}')
insert into t_checksite values (2,'{经胸超声心动图;甲状腺及颈部淋巴结;颈总动脉;}')
insert into t_checksite values (3,'{经胸超声心动图;甲状腺及颈部淋巴结;}')
------------------- 在tempdb库创建 测试表 END -----------------------
GO
--1. 创建fn_Split函数. ( 切分字符串， 返回一个列名为id的表 )  
IF OBJECT_ID('fn_Split') IS NOT NULL DROP FUNCTION fn_Split
GO  
CREATE FUNCTION [dbo].[fn_Split]
(
	@str           VARCHAR(MAX),
	@separator     VARCHAR(10)
)
RETURNS TABLE
AS
	RETURN 
	(
	    --Example:  SELECT id FROM fn_Split('a,b,d,c',',')  
	    SELECT B.id
	    FROM   (
	               (
	               	   --A　的作用只是生成 '<v>a</v><v>b</v><v>d</v><v>c</v>' 的XML格式的数据, 提供数据源 
	                   SELECT [value] = CONVERT(XML, '<v>' + REPLACE(@str, @separator, '</v><v>') + '</v>')
	               ) A 
	               OUTER APPLY
	               (
	               	   --B 的作用是将A中的 XML 数据的值枚举出来转换成行
	                   SELECT id = N.v.value('.', 'varchar(100)') FROM   A.[value].nodes('/v') N(v)
	               ) B
	           )
	)
GO
--2. 查询
SELECT a.[StudyIdentity]
	,'{'+f.id+';}' AS [checksite]
FROM t_checksite AS a CROSS APPLY dbo.fn_split( REPLACE(REPLACE(a.CheckSite,'{',''),';}',''),';') AS f
/*
StudyIdentity	checksite
1	            {经胸超声心动图;}
2	            {经胸超声心动图;}
2	            {甲状腺及颈部淋巴结;}
2	            {颈总动脉;}
3	            {经胸超声心动图;}
3	            {甲状腺及颈部淋巴结;}
*/