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

------解决方案--------------------
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]
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

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

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

------解决方案--------------------
SQL code

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小号