有一表A
name age
AAA 55
BBB 88
JJJ 0
555 43
...
还有很多行
要怎样得到 naem age name age
AAA 55 JJJ 0
BBB 88 555 43
...
每4行变2行 ....
name age
AAA 55
BBB 88
JJJ 0
555 43
...
还有很多行
要怎样得到 naem age name age
AAA 55 JJJ 0
BBB 88 555 43
...
每4行变2行 ....
select
max(case (px - 1) % 2 when 0 then name end) [name1],
max(case (px - 1) % 2 when 0 then age end) [age2],
max(case (px - 1) % 2 when 1 then name end) [name2],
max(case (px - 1) % 2 when 0 then age end) [age1]
from
(
select t.* , px = (select count(1) from a where name < t.name) + 1 from a t
) m
group by (px - 1) / 2
with cte as
(
select row_number() over(order by getdate()) id,name,age
)
select t1.*,t2.*
from cte t1
left join cte t2 on t1.id +1=t2.id
where t1%2=1 and t2%2=0
insert into a values('AAA', 55)
insert into a values('BBB', 88)
insert into a values('JJJ', 0)
insert into a values('555', 43)
go--sql 2000
select
max(case (px - 1) % 2 when 0 then name end) [name1],
max(case (px - 1) % 2 when 0 then age end) [age2],
max(case (px - 1) % 2 when 1 then name end) [name2],
max(case (px - 1) % 2 when 0 then age end) [age1]
from
(
select t.* , px = (select count(1) from a where name < t.name) + 1 from a t
) m
group by (px - 1) / 2drop table a/*
name1 age2 name2 age1
---------- ----------- ---------- -----------
555 43 AAA 43
BBB 88 JJJ 88(所影响的行数为 2 行)
*/
-- Author : htl258(Tony)
-- Date : 2010-04-26 09:26:25
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([name] NVARCHAR(10),[age] INT)
INSERT [tb]
SELECT 'AAA',55 UNION ALL
SELECT 'BBB',88 UNION ALL
SELECT 'JJJ',0 UNION ALL
SELECT '555',43
GO
--SELECT * FROM [tb]-->SQL查询如下:
;with t as
(
select rn=ROW_NUMBER()over(order by getdate()),*
from tb
)
select max(case (rn+1)%2 when 0 then [name] end) name1,
max(case (rn+1)%2 when 0 then [age] end) age1,
max(case (rn+1)%2 when 1 then [name] end) name2,
max(case (rn+1)%2 when 1 then [age] end) age2
from t
group by (rn+1)/2
/*
name1 age1 name2 age2
---------- ----------- ---------- -----------
AAA 55 BBB 88
JJJ 0 555 43
警告: 聚合或其他 SET 操作消除了 Null 值。(2 行受影响)
*/
create table a(name varchar(10), age int)
insert into a values('AAA', 55)
insert into a values('BBB', 88)
insert into a values('JJJ', 0)
insert into a values('555', 43)
go
select
max(case (px - 1) % 2 when 0 then name end) [name1],
max(case (px - 1) % 2 when 0 then age end) [age2],
max(case (px - 1) % 2 when 1 then name end) [name2],
max(case (px - 1) % 2 when 0 then age end) [age1]
from
(
select t.* , px = row_number() over(order by name) from a t
) m
group by (px - 1) / 2drop table a/*
name1 age2 name2 age1
---------- ----------- ---------- -----------
555 43 AAA 43
BBB 88 JJJ 88
警告: 聚合或其他 SET 操作消除了空值。(2 行受影响)
*/
--SQL 2000
create table a(name varchar(10), age int)
insert into a values('AAA', 55)
insert into a values('BBB', 88)
insert into a values('JJJ', 0)
insert into a values('555', 43)
go--sql 2000
select
max(case (px - 1) % 2 when 0 then name end) [name1],
max(case (px - 1) % 2 when 0 then age end) [age2],
max(case (px - 1) % 2 when 1 then name end) [name2],
max(case (px - 1) % 2 when 1 then age end) [age1]
from
(
select t.* , px = (select count(1) from a where name < t.name) + 1 from a t
) m
group by (px - 1) / 2drop table a/*
name1 age2 name2 age1
---------- ----------- ---------- -----------
555 43 AAA 55
BBB 88 JJJ 0(所影响的行数为 2 行)*/--SQL 2005
create table a(name varchar(10), age int)
insert into a values('AAA', 55)
insert into a values('BBB', 88)
insert into a values('JJJ', 0)
insert into a values('555', 43)
go--sql 2000
select
max(case (px - 1) % 2 when 0 then name end) [name1],
max(case (px - 1) % 2 when 0 then age end) [age2],
max(case (px - 1) % 2 when 1 then name end) [name2],
max(case (px - 1) % 2 when 1 then age end) [age1]
from
(
select t.* , px = row_number() over(order by name) from a t
) m
group by (px - 1) / 2drop table a/*
name1 age2 name2 age1
---------- ----------- ---------- -----------
555 43 AAA 55
BBB 88 JJJ 0
*/