-- 建表为
CREATE TABLE [dbo].[Test_Table](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TestName] [nvarchar](50) NULL,
[TestCode] [nvarchar](50) NULL
) ON [PRIMARY]GOinsert [Test_Table] select 'a1,b2,c3,d4,e5','A' union all
select '1a,2b,3c','B' union all
select '2a,3b,4c,5d','C'select * from [Test_Table]
--
--ID TestName TestCode
--1 a1,b2,c3,d4,e5 A
--2 1a,2b,3c B
--3 2a,3b,4c,5d C
--想要的查询结果为:请高手指点指点--ID TestName TestCode
--1 a1 A
--1 b2 A
--1 c3 A
--1 d4 A
--1 e5 A
--2 1a B
--2 2b B
--2 3c B
--3 2a C
--3 3b C
--3 4c C
--3 5d C
select substring(TestName,number,charindex(',',TestName +',')-1), t.*
from [Test_Table] t , master..spt_values spt
where LEN(t.testName) > spt.number
and spt.type = 'p'
and substring(',' + TestName +',',number,charindex(',',TestName +',')+1) like ',%,'
SELECT a.ID,a.TestCode,e.n FROM (
select *,CONVERT(XML,'<n>'+REPLACE(t.TestName,',','</n><n>')+'</n>') as x from [Test_Table] AS t
) AS a
CROSS APPLY (SELECT s.b.value('.','varchar(100)') AS n FROM a.x.nodes('n') s(b)) e/*
ID TestCode n
1 A a1
1 A b2
1 A c3
1 A d4
1 A e5
2 B 1a
2 B 2b
2 B 3c
3 C 2a
3 C 3b
3 C 4c
3 C 5d
*/
合并分拆表_整理贴1
http://bbs.csdn.net/topics/230087434
;WITH roy
AS ( SELECT [ID]
,[TestName] = CAST(LEFT([TestName] ,
CHARINDEX(',' ,
[TestName] + ',') - 1) AS NVARCHAR(100))
,Split = CAST(STUFF([TestName] + ',' , 1 ,
CHARINDEX(',' , [TestName] + ',') ,
'') AS NVARCHAR(100))
,[TestCode]
FROM [Test_Table]
UNION ALL
SELECT [ID]
,[TestName] = CAST(LEFT(Split ,
CHARINDEX(',' , Split) - 1) AS NVARCHAR(100))
,Split = CAST(STUFF(Split , 1 , CHARINDEX(',' , Split) ,
'') AS NVARCHAR(100))
,[TestCode]
FROM roy
WHERE Split > '' )
SELECT [ID]
,[TestName]
,[TestCode]
FROM roy
ORDER BY [ID]
OPTION ( MAXRECURSION 0 );/*
ID TestName TestCode
1 a1 A
1 b2 A
1 c3 A
1 d4 A
1 e5 A
2 1a B
2 2b B
2 3c B
3 2a C
3 3b C
3 4c C
3 5d C
*/