有一表: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请教!
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请教!
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
*/
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',30select * 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
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小号