有表tb, 如下:
id values
----------- -----------
1 aa,bb
2 aaa,bbb,ccc欲按,分拆values列, 分拆后结果如下:
id value
----------- --------
1 aa
1 bb
2 aaa
2 bbb
2 ccc之前的老办法, 一般是使用临时表
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','
DROP TABLE #
id values
----------- -----------
1 aa,bb
2 aaa,bbb,ccc欲按,分拆values列, 分拆后结果如下:
id value
----------- --------
1 aa
1 bb
2 aaa
2 bbb
2 ccc之前的老办法, 一般是使用临时表
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','
DROP TABLE #
没有的,可以补null。
第一次碰到这种连第一范式都不遵守的数据库阿,汗..........
(a int,b varchar(50),c varchar(50))
insert test
select 1, '1;2;3;4', 'aa;bb;cc;dd;' union
select 2, '1;2', 'ee;ff;'
declare @s varchar(2000)
set @s='select [a]='''
select @s=@s+rtrim(a)+''','''+replace(b,';','''[b] union all select '''+rtrim(a)+''',''')+
''' union all select ''' from test
set @s='select id=identity(int,1,1),* into t1 from ('+left(@s,len(rtrim(@s))-18)+')te'
exec (@s)declare @s2 varchar(2000)
set @s2='select [a]='''
select @s2=@s2+rtrim(a)+''','''+replace(c,';','''[c] union all select '''+rtrim(a)+''',''')+
''' union all select ''' from test
set @s2='select id=identity(int,1,1),* into t2 from ('+left(@s2,len(rtrim(@s2))-18)+')te where [c]<>'''''
exec (@s2)select a.a,a.b,b.c from t1 a left join t2 b on a.id=b.id
drop table test,t1,t2
----------------------
a b c
---- ---- ----
1 1 aa
1 2 bb
1 3 cc
1 4 dd
2 1 ee
2 2 ff