sql如何拆分带逗号的列

Question

列1                列2
aa,kk              1
aaaa,bbbbb,cccc    2
效果这一行就变成多行
aa      1
kk      1
aaaa    2
bbbb    2
cccc    2
如何查询出这结果,或者新建一个表是那个带逗号记录表的拆分,都行

Answer 1

CREATE TABLE #(x VARCHAR(100))
INSERT # SELECT '1,2,3,4,5,6,7,8,9'
UNION ALL SELECT '34,22,112421,6745'
UNION ALL SELECT '34,3'
SELECT SUBSTRING(x,id,CHARINDEX(',',x+',',id)-id) val
FROM # a,
(SELECT DISTINCT langid id FROM master.dbo.syslanguages WHERE langid<200)b
WHERE SUBSTRING(','+x,id,1)=','DROP TABLE #--result
/*val                            
------------------------------ 
1
2
3
4
5
6
7
8
9
34
22
112421
6745
34
3（所影响的行数为 15 行）*/

Answer 2

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-10-16 11:44: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]([col1] varchar(15),[col2] int)
insert [tb]
select 'aa,kk',1 union all
select 'aaaa,bbbbb,cccc',2
--------------开始查询--------------------------select 
    a.COl2,b.Col1
from 
    (select Col2,COl1=convert(xml,'<root><v>'+replace(COl1,',','</v><v>')+'</v></root>') from Tb)a
outer apply
    (select Col1=C.v.value('.','nvarchar(100)') from a.COl1.nodes('/root/v')C(v))b
----------------结果----------------------------
/* COl2        Col1
----------- ----------------------------------------------------------------------------------------------------
1           aa
1           kk
2           aaaa
2           bbbbb
2           cccc(5 行受影响)*/

Answer 3

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-10-16 11:44: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]([col1] varchar(15),[col2] int)
insert [tb]
select 'aa,kk',1 union all
select 'aaaa,bbbbb,cccc',2
--------------开始查询--------------------------select 
   b.Col1 ,a.COl2
from 
    (select Col2,COl1=convert(xml,'<root><v>'+replace(COl1,',','</v><v>')+'</v></root>') from Tb)a
outer apply
    (select Col1=C.v.value('.','nvarchar(100)') from a.COl1.nodes('/root/v')C(v))b
----------------结果----------------------------
/* Col1                                                                                                 COl2
---------------------------------------------------------------------------------------------------- -----------
aa                                                                                                   1
kk                                                                                                   1
aaaa                                                                                                 2
bbbbb                                                                                                2
cccc                                                                                                 2(5 行受影响)*/