sql 表拆分问题，麻烦各位大狭紧急相助，跪谢！！

Question

原始表table1如下：
SID ID Material Qty Serial SerialID T1
S0001 001 M1         1 H1
S0002 001 M2         2 H2,H3
S0003 001 M3         3 T1,T3,T5
S0004 002 M1         4 G1,G3,G8,G6
S0005 002 M5         1 A1
S0006 003 M6         2 B1,B2
S0008 003 M8         3 P01,P002,PA01 运算的规则：
1）判断Qty是否为1，如果是1，SerialID=Serial,T1=ID+Material+SerialID
2) 如果Qty大于1，则要将行拆分，如：第2行Qty=2,(Serial=H2,H3),则需要拆分成2行，
   第1行SerialID=H2,第1行的T1=ID+Material+SerialID，即T1=001-M2-H2，
   第2行SerialID=H3，第2行的T1=ID+Material+SerialID，即T1=001-M2-H3依次类推：要求得到下面的结果：SID ID Material Qty Serial         SerialID T1
S0001 001 M1          1 H1            H1         001-M1-H1
S0002 001 M2          1 H2,H3            H2         001-M2-H2
S0002 001 M2          1 H3,H3            H3         001-M2-H3
S0003 001 M3          1 T1,T3,T5    T1         001-M3-T1
S0003 001 M3          1 T1,T3,T5    T3         001-M3-T3
S0003 001 M3          1 T1,T3,T5    T5         001-M3-T5
S0004 002 M1          1 G1,G3,G8,G6    G1         002-M1-G1
S0004 002 M1          1 G1,G3,G8,G6    G3         002-M1-G3
S0004 002 M1          1 G1,G3,G8,G6    G8         002-M1-G8
S0004 002 M1          1 G1,G3,G8,G6    G6         002-M1-G6
S0005 002 M5          1 A1            A1         002-M5-A1
S0006 003 M6          1 B1,B2            B1         003-M6-B1
S0006 003 M6          1 B1,B2            B2         003-M6-B2
S0008 003 M8          1 P01,P002,PA01    P01         003-M8-P01
S0008 003 M8          1 P01,P002,PA01    P002         003-M8-P002
S0008 003 M8          1 P01,P002,PA01    PA01         003-M8-PA01
紧急，麻烦大侠相助，跪谢！

Answer 1

原始表table1如下： 
SID   ID   Material     Qty     Serial        SerialID   T1 
S0001 001     M1         1      H1 
S0002 001     M2         2      H2,H3 
S0003 001     M3         3      T1,T3,T5 
S0004 002     M1         4      G1,G3,G8,G6 
S0005 002     M5         1      A1 
S0006 003     M6         2      B1,B2 
S0008 003     M8         3      P01,P002,PA01 运算的规则： 
1）判断Qty是否为1，如果是1，SerialID=Serial,T1=ID+Material+SerialID 
2) 如果Qty大于1，则要将行拆分，如：第2行Qty=2,(Serial=H2,H3),则需要拆分成2行， 
   第1行SerialID=H2,第1行的T1=ID+Material+SerialID，即T1=001-M2-H2， 
   第2行SerialID=H3，第2行的T1=ID+Material+SerialID，即T1=001-M2-H3 依次类推： 要求得到下面的结果： SID      ID   Material      Qty    Serial        SerialID      T1 
S0001    001     M1          1      H1            H1           001-M1-H1 
S0002    001     M2          1      H2,H3         H2           001-M2-H2 
S0002    001     M2          1      H3,H3         H3           001-M2-H3 
S0003    001     M3          1      T1,T3,T5      T1           001-M3-T1 
S0003    001     M3          1      T1,T3,T5      T3           001-M3-T3 
S0003    001     M3          1      T1,T3,T5      T5           001-M3-T5 
S0004    002     M1          1      G1,G3,G8,G6   G1           002-M1-G1 
S0004    002     M1          1      G1,G3,G8,G6   G3           002-M1-G3 
S0004    002     M1          1      G1,G3,G8,G6   G8           002-M1-G8 
S0004    002     M1          1      G1,G3,G8,G6   G6           002-M1-G6 
S0005    002     M5          1      A1            A1           002-M5-A1 
S0006    003     M6          1      B1,B2         B1           003-M6-B1 
S0006    003     M6          1      B1,B2         B2           003-M6-B2 
S0008    003     M8          1     P01,P002,PA01  P01          003-M8-P01 
S0008    003     M8          1     P01,P002,PA01  P002         003-M8-P002 
S0008    003     M8          1     P01,P002,PA01  PA01         003-M8-PA01 

Answer 2

实际上就是一个字符串的拆分问题,实际上你Quantity的判断没有太大意义,
直接将Serial按照','拆分即可SearialID列,然后通过简单的字符串相加就可以得到T1列,从而得到你的结果,
请参考以下的链接,关于字符串拆分的,讲的很详细:
http://topic.csdn.net/u/20070813/14/401e82ac-4770-4ae6-987f-034afee36e4c.html

Answer 3

用 case ...when...then ...else ...end就可以解决你的问题啊.自己判断就对了

Answer 4

USE [CSDN]
GO
IF OBJECT_ID(N'A') IS NOT NULL
BEGIN
   DROP TABLE A
END
GO
CREATE TABLE A(SID nvarchar(10),ID nvarchar(10),Material nvarchar(10),Qty int, Serial nvarchar(20), SerialID nvarchar(10),T1 nvarchar(20))
go
INSERT INTO A(SID,ID,Material,Qty,Serial) VALUES('S0001', '001','M1',1,'H1')
INSERT INTO A(SID,ID,Material,Qty,Serial) VALUES('S0002', '001','M2',2,'H2,H3')
INSERT INTO A(SID,ID,Material,Qty,Serial) VALUES('S0003', '001','M3',3,'T1,T3,T5')
INSERT INTO A(SID,ID,Material,Qty,Serial) VALUES('S0004', '002','M1',4,'G1,G3,G8,G6')
INSERT INTO A(SID,ID,Material,Qty,Serial) VALUES('S0005', '002','M5',1,'A1')
INSERT INTO A(SID,ID,Material,Qty,Serial) VALUES('S0006', '003','M6',2,'B1,B2')
INSERT INTO A(SID,ID,Material,Qty,Serial) VALUES('S0008', '003','M8',3,'P01,P002,PA01')
GOSELECT   TOP   8000 id   =   identity(int,1,1)   INTO   #   FROM   syscolumns   a,   syscolumns   b  SELECT   
        A.SID,A.ID,A.Material, 1 as qty,A.Serial,   
        SerialID   =   SUBSTRING(A.Serial,B.ID,CHARINDEX( ',',A.Serial+',',B.ID) - B.ID),
        T1 = convert(nvarchar(10),A.ID)+ '-' + convert(nvarchar(10),A.Material) + '-' + SUBSTRING(A.Serial,B.ID,CHARINDEX( ',',A.Serial+',',B.ID) - B.ID)
FROM   A   A,   #   B 
WHERE   SUBSTRING( ',' + A.Serial, B.ID,1) =','  
GO 
/*
SID        ID         Material   qty         Serial               SerialID             T1
---------- ---------- ---------- ----------- -------------------- -------------------- ------------------------------------------
S0001      001        M1         1           H1                   H1                   001-M1-H1
S0002      001        M2         1           H2,H3                H2                   001-M2-H2
S0002      001        M2         1           H2,H3                H3                   001-M2-H3
S0003      001        M3         1           T1,T3,T5             T1                   001-M3-T1
S0003      001        M3         1           T1,T3,T5             T3                   001-M3-T3
S0003      001        M3         1           T1,T3,T5             T5                   001-M3-T5
S0004      002        M1         1           G1,G3,G8,G6          G1                   002-M1-G1
S0004      002        M1         1           G1,G3,G8,G6          G3                   002-M1-G3
S0004      002        M1         1           G1,G3,G8,G6          G8                   002-M1-G8
S0004      002        M1         1           G1,G3,G8,G6          G6                   002-M1-G6
S0005      002        M5         1           A1                   A1                   002-M5-A1
S0006      003        M6         1           B1,B2                B1                   003-M6-B1
S0006      003        M6         1           B1,B2                B2                   003-M6-B2
S0008      003        M8         1           P01,P002,PA01        P01                  003-M8-P01
S0008      003        M8         1           P01,P002,PA01        P002                 003-M8-P002
S0008      003        M8         1           P01,P002,PA01        PA01                 003-M8-PA01(16 row(s) affected)*/drop table #

Answer 5

--用动态SQL：CREATE TABLE A(SID nvarchar(10),ID nvarchar(10),Material nvarchar(10),Qty int, Serial nvarchar(20), SerialID nvarchar(10),T1 nvarchar(20))
INSERT INTO A(SID,ID,Material,Qty,Serial) VALUES('S0001', '001','M1',1,'H1')
INSERT INTO A(SID,ID,Material,Qty,Serial) VALUES('S0002', '001','M2',2,'H2,H3')
INSERT INTO A(SID,ID,Material,Qty,Serial) VALUES('S0003', '001','M3',3,'T1,T3,T5')
INSERT INTO A(SID,ID,Material,Qty,Serial) VALUES('S0004', '002','M1',4,'G1,G3,G8,G6')
INSERT INTO A(SID,ID,Material,Qty,Serial) VALUES('S0005', '002','M5',1,'A1')
INSERT INTO A(SID,ID,Material,Qty,Serial) VALUES('S0006', '003','M6',2,'B1,B2')
INSERT INTO A(SID,ID,Material,Qty,Serial) VALUES('S0008', '003','M8',3,'P01,P002,PA01')
GO
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+replace(serial+',',',','''p,'''+sid+'''d union all select ''') from a
set @sql='select '''+@sql
set @sql=left(@sql,len(@sql)-19)
exec('select sid,id,Material,1[Qty],Serial,p[SerialID],id+''-''+Material+''-''+p[T1]
 from a,('+@sql+')b where sid=d') 

Answer 6

CREATE TABLE TA(SID nvarchar(10),ID nvarchar(10),Material nvarchar(10),Qty int, Serial nvarchar(20), SerialID nvarchar(10),T1 nvarchar(20))
go
INSERT INTO TA(SID,ID,Material,Qty,Serial) VALUES('S0001', '001','M1',1,'H1')
INSERT INTO TA(SID,ID,Material,Qty,Serial) VALUES('S0002', '001','M2',2,'H2,H3')
INSERT INTO TA(SID,ID,Material,Qty,Serial) VALUES('S0003', '001','M3',3,'T1,T3,T5')
INSERT INTO TA(SID,ID,Material,Qty,Serial) VALUES('S0004', '002','M1',4,'G1,G3,G8,G6')
INSERT INTO TA(SID,ID,Material,Qty,Serial) VALUES('S0005', '002','M5',1,'A1')
INSERT INTO TA(SID,ID,Material,Qty,Serial) VALUES('S0006', '003','M6',2,'B1,B2')
INSERT INTO TA(SID,ID,Material,Qty,Serial) VALUES('S0008', '003','M8',3,'P01,P002,PA01')
GOSELECT TOP 8000 id = identity(int,1,1) INTO # FROM syscolumns a,syscolumns b  select *,T1 = ltrim(id)+'-'+Material+'-'+SerialID
from
(select a.SID,a.ID,a.Material,1 as Qty,Serial,
 SerialID=substring(a.Serial,b.id,charindex(',',a.Serial+',',b.id)-b.id)
from ta a, # b
where substring(','+a.Serial,b.id,1)=','
    ) c

order by c.sidDROP TABLE TA,#/*
SID        ID         Material   Qty         Serial               SerialID             T1                                         
---------- ---------- ---------- ----------- -------------------- -------------------- ------------------------------------------ 
S0001      001        M1         1           H1                   H1                   001-M1-H1
S0002      001        M2         1           H2,H3                H2                   001-M2-H2
S0002      001        M2         1           H2,H3                H3                   001-M2-H3
S0003      001        M3         1           T1,T3,T5             T1                   001-M3-T1
S0003      001        M3         1           T1,T3,T5             T3                   001-M3-T3
S0003      001        M3         1           T1,T3,T5             T5                   001-M3-T5
S0004      002        M1         1           G1,G3,G8,G6          G1                   002-M1-G1
S0004      002        M1         1           G1,G3,G8,G6          G3                   002-M1-G3
S0004      002        M1         1           G1,G3,G8,G6          G8                   002-M1-G8
S0004      002        M1         1           G1,G3,G8,G6          G6                   002-M1-G6
S0005      002        M5         1           A1                   A1                   002-M5-A1
S0006      003        M6         1           B1,B2                B1                   003-M6-B1
S0006      003        M6         1           B1,B2                B2                   003-M6-B2
S0008      003        M8         1           P01,P002,PA01        P01                  003-M8-P01
S0008      003        M8         1           P01,P002,PA01        P002                 003-M8-P002
S0008      003        M8         1           P01,P002,PA01        PA01                 003-M8-PA01（所影响的行数为 16 行）
*/