22,300
社区成员




IF OBJECT_ID('TAB')IS NOT NULL DROP TABLE TAB
GO
CREATE TABLE TAB(col1 VARCHAR(10), col2 VARCHAR(10), col3 VARCHAR(25), col4 VARCHAR(10), col5 NVARCHAR(5))
INSERT TAB SELECT 'abcd', 'lllls', '123,456,789,123', 'myw', N'备注'
ALTER TABLE TAB
ADD ID INT IDENTITY
GO
if object_id('Tempdb..#Num') is not null
drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select
a.Col1,COL2,COl3=substring(a.Col3,b.ID,charindex(',',a.Col3+',',b.ID)-b.ID) ,COL4,COL5
from
Tab a,#Num b
where
charindex(',',','+a.Col3,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
/*Col1 COL2 COl3 COL4 COL5
---------- ---------- ------------------------- ---------- -----
abcd lllls 123 myw 备注
abcd lllls 456 myw 备注
abcd lllls 789 myw 备注
abcd lllls 123 myw 备注*/
拆分表:
--> --> (Roy)生成測試數據
if not object_id('Tab') is null
drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
Go
SQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select
a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID)
from
Tab a,#Num b
where
charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
SQL2005用Xml:
select
a.COl1,b.Col2
from
(select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
outer apply
(select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
SQL05用CTE:
;with roy as
(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
union all
select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)
生成结果:
/*
Col1 COl2
----------- -----
1 a
1 b
1 c
2 d
2 e
3 f
*/