CREATE TABLE [dbo].[testtb](
[namea] [varchar](100) NULL,
[desca] [varchar](50) NULL
) ON [PRIMARY]
insert into testtb values(‘A1,A2,‘,‘AAAAA‘)
insert into testtb values(‘A3,‘,‘BBBBBB‘)
insert into testtb values(‘A5,A8,A9,‘,‘AAACCAA‘)
insert into testtb values(‘A6,A20,‘,‘AAANNNNAA‘)
--select * from testtb
--truncate table testtb
--定义一个临时表
WITH testtb2 AS (
SELECT namea,desca,CHARINDEX(‘,‘,namea) STA,CHARINDEX(‘,‘,namea)-1 LENS FROM testtb
UNION ALL
SELECT namea,desca,CHARINDEX(‘,‘,namea,STA+1) STA,CHARINDEX(‘,‘,namea,STA+1)-STA-1 LENS FROM testtb2 WHERE STA<>0)
--SELECT * FROM testtb2
SELECT SUBSTRING(namea,STA-LENS,LENS) as [name],desca FROM testtb2
WHERE STA<>0
order by SUBSTRING(namea,STA-LENS,LENS)
SQL 将一个字段内用逗号分隔的内容分成多条记录
原文：http://www.cnblogs.com/Snowfun/p/5875156.html
