字符分离，生成新的记录

[sunylf]

select 'A001','A'
union all select 'A002','D'
union all select 'A003','C,B'
union all select 'A004','G,F,E'
union all select 'A005','H'

如何能得到以下记录?
'A001','A'
'A002','D'
'A003','C'
'A003','B'
'A004','G'
'A004','F'
'A004','E'
'A005','H'

[zc_0101]

拆分表:

--> --> (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
*/

[sunylf]

补充：sql server 2000数据库。

[playwarcraft]

--SQL2000的例子
Create table T(name nvarchar(50),amount int)
insert into T
    select 'a,b,c' ,1
union all     select 'd,f',2
union all     select 'g',3
GO

SELECT
    SUBSTRING(A.name,B.number,CHARINDEX(',',A.name+',',B.number)-B.number) AS [name],
    amount
FROM  T as  A
    JOIN master.dbo.spt_values AS B
        ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.name)
            AND SUBSTRING(','+A.name,B.number,1)=','

/*
name                                               amount      
-------------------------------------------------- ----------- 
a                                                  1
b                                                  1
c                                                  1
d                                                  2
f                                                  2
g                                                  3
*/

Drop table T

[htl258_Tony]

IF OBJECT_ID('[dbo].[fn_SplitStringToTable]')>0
	DROP FUNCTION [dbo].[fn_SplitStringToTable]
GO
CREATE FUNCTION [dbo].[fn_SplitStringToTable]
(
    @InputString VARCHAR(8000),
    @Delimeter   CHAR(1)
)
RETURNS @Table TABLE
(
    FSequence INT IDENTITY(1, 1),
    FValue    VARCHAR(511),
    FPosition INT,
    FLength   INT
)
BEGIN
    DECLARE @Position INT
    
    SELECT @Position = 1
    
    IF RIGHT(@InputString, LEN(@Delimeter)) <> @Delimeter
        SET @InputString = @InputString + @Delimeter
    
    WHILE SUBSTRING(@InputString, @Position, 1) = CHAR(10) OR
          SUBSTRING(@InputString, @Position, 1) = CHAR(13)
    BEGIN
        SELECT @Position = @Position + 1
    END
    
    WHILE @Position <= CHARINDEX(@Delimeter, @InputString, @Position)
    BEGIN
        INSERT INTO @Table(FValue, FPosition, FLength)
             VALUES (RTRIM(LTRIM(SUBSTRING(@InputString,
                                           @Position,
                                           CHARINDEX(@Delimeter,
                                                     @InputString,
                                                     @Position) - @Position))), @Position, CHARINDEX(@Delimeter, @InputString, @Position) - @Position)
        SELECT @Position = CHARINDEX(@Delimeter, @InputString, @Position) + 1
        
        WHILE SUBSTRING(@InputString, @Position, 1) = CHAR(10) OR
              SUBSTRING(@InputString, @Position, 1) = CHAR(13)
        BEGIN
            SELECT @Position = @Position + 1
        END
    END
    RETURN
END
GO

SELECT a.a, b.FValue 
FROM (
         SELECT 'A001' a, 'A' b
         UNION ALL SELECT 'A002', 'D'
         UNION ALL SELECT 'A003', 'C,B'
         UNION ALL SELECT 'A004', 'G,F,E'
         UNION ALL SELECT 'A005', 'H'
     ) a
	CROSS APPLY [dbo].[fn_SplitStringToTable](b, ',') b
/*
a    FValue
---- ---------------
A001 A
A002 D
A003 C
A003 B
A004 G
A004 F
A004 E
A005 H

(8 行受影响)
*/

[lcw321321]

一般情况下都是自己写一个自定义函数来实现