比如有两个字条串:
@a1='ab,c,d,'
@b1='12,0,34,'create table #t1(a1 varchar(30),b1 decimal(18,2))我想把@a1和@b1的数据插入临时表#t1,使得#t1表变成:
a1 b1
ab 12
c 0
d 34请问如何写sql代码?
@a1='ab,c,d,'
@b1='12,0,34,'create table #t1(a1 varchar(30),b1 decimal(18,2))我想把@a1和@b1的数据插入临时表#t1,使得#t1表变成:
a1 b1
ab 12
c 0
d 34请问如何写sql代码?
标题:分拆列值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)
)BDROP TABLE tb/*
id value
----------- ------------------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc(5 行受影响)
*/