分拆列值整理（1）

--分拆列值（1）

--

--原著：邹建

--整理于2009-03-09 17:21:25.793 by select left('claro',2)

--Microsoft SQL Server 2005 - 9.00.3042.00

-- (Intel X86)?? Feb? 9 2007 22:47:07?? Copyright (c) 1988-2005

-- Microsoft Corporation? Express Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

?

--有表tb,如下:

/*

id????????? value

----------- -----------

1????????? aa,bb

2????????? aaa,bbb,ccc

欲按id,分拆value列,分拆后结果如下:

id????????? value

----------- --------

1????????? aa

1????????? bb

2????????? aaa

2????????? bbb

2????????? ccc

--??????????????? */

/*******

create table tb(id int,value varchar(30))

insert into tb values(1,'aa,bb')

insert into tb values(2,'aaa,ccc')

GO

DROP TABLE tb

GO

SELECT TOP 8000 id = IDENTITY(int,1,1) INTO # FROM syscolumns a,syscolumns b

GO

DROP TABLE #

*******/

?

-->1. (sql server 2000)

?

SELECT A.id, SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ', B.id) - B.id)? [value]

FROM tb A, # B

WHERE SUBSTRING(',' + A.[value], 1) = ','

/*

id? value

1?? aa

1?? bb

2?? aaa

2?? bbb

2?? ccc

*/

?

-->2. (sql server 2005)

?

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

?

/*

id? value

1?? aa

1?? bb

2?? aaa

2?? bbb

2?? ccc

*/

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!