把表由一行 变 二行
有一表：table1
code name total
0001 品名1 10
0001 品名1 20
0002 品名2 10
0002 品名2 20
0001 品名1 30

想把上面的表变两行，按code分组，同一组可分放两行，不同组的换新行。
如下表结果.（生成另一新表table2）
code name total code name total
0001 品名1 10 0001 品名1 20
0001 品名1 30
0002 品名2 10 0002 品名2 20

请教！

[解决办法]

SQL code

--> --> (Roy)生成 if not object_id('Tempdb..#T') is null    drop table #TGoCreate table #T([code] nvarchar(4),[name] nvarchar(3),[total] int)Insert #Tselect N'0001',N'品名1',10 union allselect N'0001',N'品名1',20 union allselect N'0002',N'品名2',10 union allselect N'0002',N'品名2',20 union allselect N'0001',N'品名1',30Gowith aas(select *from (select [code],[name],[total],ROW_NUMBER()over(partition by [code] order by row) as Row,ROW_NUMBER()over(partition by [code] order by row) -Row as grpfrom (Select *,row=ROW_NUMBER()over(order by (select 1)) from #T)t)t2)select a.code,a.name,a.total,b.code,b.name,b.totalfrom a left join a as b on a.grp=b.grp and a.code=b.code and a.Row=b.Row-1where a.Row%2=1/*code    name    total    code    name    total0001    品名1    10    0001    品名1    200001    品名1    30    NULL    NULL    NULL0002    品名2    10    0002    品名2    20*/
[解决办法]
SQL code
-----------------------------------  Author: HEROWANG(让你望见影子的墙)--  Date  : 2012-04-20 08:15:47--  blog  : blog.csdn.net/herowang--------------------------------- IF OBJECT_ID('[tb]') IS NOT NULL     DROP TABLE [tb]goCREATE TABLE [tb] (code VARCHAR(4),name VARCHAR(5),total INT)INSERT INTO [tb]SELECT '0001','品名1',10 UNION ALLSELECT '0001','品名1',20 UNION ALLSELECT '0002','品名2',10 UNION ALLSELECT '0002','品名2',20 UNION ALLSELECT '0001','品名1',30select * from [tb]gowith cte as(select row=row_number() over(partition by code order by getdate() ),* from tb)select * from cte s left join cte t on s.code=t.code and s.row=t.row-1where s.row %2=1 1    0001    品名1    10    2    0001    品名1    203    0001    品名1    30    NULL    NULL    NULL    NULL1    0002    品名2    10    2    0002    品名2    20
[解决办法]
SQL code
--2000 可以用这方法，获得序列值；剩下的思路同楼上各位row= (select Count(1) +1 from tb)
[解决办法]
SQL code
IF OBJECT_ID('[tb]') IS NOT NULL     DROP TABLE [tb]goCREATE TABLE [tb] (code VARCHAR(4),name VARCHAR(5),total INT)INSERT INTO [tb]SELECT '0001','品名1',10 UNION ALLSELECT '0001','品名1',20 UNION ALLSELECT '0002','品名2',10 UNION ALLSELECT '0002','品名2',20 UNION ALLSELECT '0001','品名1',30goalter table tb add id int identitygoselect s.code,s.name,s.total,t.code,t.name,t.total from (select row=(select COUNT(1) from tb b where a.code=b.code and b.id<=a.id),* from tb a) s left join (select row=(select COUNT(1) from tb b where a.code=b.code and b.id<=a.id),* from tb a)t  on s.code=t.code and s.row=t.row-1where s.row %2=1 order by s.code/*code    name    total    code    name    total0001    品名1    10    0001    品名1    200001    品名1    30    NULL    NULL    NULL0002    品名2    10    0002    品名2    20*/goalter table tb drop column iddrop table tb--接楼上测试数据用了。--TravyLee小号