34,590
社区成员
发帖
与我相关
我的任务
分享
declare @str varchar(8000)
set @str = 'aaa,bb,ccc,dd,efr'
set @str = 'select b='''+replace(@str,',',''''+' union all select ''')+''''
exec(@str)
----------------结果----------------------------
/*b
----
aaa
bb
ccc
dd
efr
(5 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-10 11:17:22
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] varchar(4),[b] varchar(17))
insert [tb]
select 'test','aaa,bb,ccc,dd,efr'
--------------开始查询--------------------------
select
a.a,c.b
from
(select a,b=convert(xml,'<root><v>'+replace(b,',','</v><v>')+'</v></root>') from Tb)a
outer apply
(select b=C.v.value('.','nvarchar(100)') from a.b.nodes('/root/v')C(v))c
----------------结果----------------------------
/*a b
---- ----------------------------------------------------------------------------------------------------
test aaa
test bb
test ccc
test dd
test efr
(5 行受影响)
*/
/*
标题:分拆列值1
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-11-20
地点:广东深圳
描述
有表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)
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. 新的解决方法(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
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
DROP TABLE tb
/*
id value
----------- ------------------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
(5 行受影响)
*/