把表由一行 变 二行

[sunfor]

有一表：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

请教！

[中国风]

没有列标识大小？

[中国风]

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
	drop table #T
Go
Create table #T([code] nvarchar(4),[name] nvarchar(3),[total] int)
Insert #T
select N'0001',N'品名1',10 union all
select N'0001',N'品名1',20 union all
select N'0002',N'品名2',10 union all
select N'0002',N'品名2',20 union all
select N'0001',N'品名1',30
Go
with a
as
(
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 grp
from (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.total
from a left join a as b on a.grp=b.grp and a.code=b.code and a.Row=b.Row-1
where a.Row%2=1

/*
code	name	total	code	name	total
0001	品名1	10	0001	品名1	20
0001	品名1	30	NULL	NULL	NULL
0002	品名2	10	0002	品名2	20
*/

[sunfor]

table2是现有的，表结构如下：（1楼的table2结构错的，没把各列区分！）
code name total code2 name2 total2
0001 品名1 10 0001 品名1 20
0001 品名1 30
0002 品名2 10 0002 品名2 20

[让你望见影子的墙]

---------------------------------
--  Author: HEROWANG(让你望见影子的墙)
--  Date  : 2012-04-20 08:15:47
--  blog  : blog.csdn.net/herowang
---------------------------------
 
IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
go
CREATE TABLE [tb] (code VARCHAR(4),name VARCHAR(5),total INT)
INSERT INTO [tb]
SELECT '0001','品名1',10 UNION ALL
SELECT '0001','品名1',20 UNION ALL
SELECT '0002','品名2',10 UNION ALL
SELECT '0002','品名2',20 UNION ALL
SELECT '0001','品名1',30

select * from [tb]

go
with 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-1
where s.row %2=1 

1	0001	品名1	10	2	0001	品名1	20
3	0001	品名1	30	NULL	NULL	NULL	NULL
1	0002	品名2	10	2	0002	品名2	20

[sunfor]

楼上两位用的是SQL2005？我用ROW_NUMBER()和row_number()提示不是可以识别的函数名！我用的是SQL2000.

[Mr_Nice]

--2000 可以用这方法，获得序列值；剩下的思路同楼上各位

row= (select Count(1) +1 from tb)

[TracyLeebaihe]

IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
go
CREATE TABLE [tb] (code VARCHAR(4),name VARCHAR(5),total INT)
INSERT INTO [tb]
SELECT '0001','品名1',10 UNION ALL
SELECT '0001','品名1',20 UNION ALL
SELECT '0002','品名2',10 UNION ALL
SELECT '0002','品名2',20 UNION ALL
SELECT '0001','品名1',30
go
alter table tb add id int identity
go
select 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-1
where s.row %2=1 order by s.code
/*
code	name	total	code	name	total
0001	品名1	10	0001	品名1	20
0001	品名1	30	NULL	NULL	NULL
0002	品名2	10	0002	品名2	20
*/
go
alter table tb drop column id
drop table tb

--接楼上测试数据用了。

--TravyLee小号

[sunfor]

多谢楼上的，但我不想加个ID，在SQL2000中，还有其它方法实现？请教 。。。