ID aname aage
1 '张三' '10'
2 '李四' '20'
3 '王五' '30'
4 '孙六' '22'
5 '赵达' '44'
6 '钱二' '21'
7 '吕宋' '35'/*
表结构最初如上所示,但是为了排版出报表,客户要求:
只能显示总记录条数的一半记录,
另外一半或者不足一半的记录行转列,排在显示出来的记录后面,如下所示结构。
*/
ID aname1 aage1 aname2 aage2
1 '张三' '10' '赵达' '44'
2 '李四' '20' '钱二' '21'
3 '王五' '30' '吕宋' '35'
4 '孙六' '22' NULL NULL
诚向各位高手请教,谢谢您的帮助!!!!
declare @t table (
ID int not null,
aname varchar(8) not null,
aage tinyint not null
)
insert into @t
select 1,'张三',10 union all
select 2,'李四',20 union all
select 3,'王五',30 union all
select 4,'孙六',22 union all
select 5,'赵达',44 union all
select 6,'钱二',21 union all
select 7,'吕宋',35
;with T1 as (
select top 50 percent
row_number() over (order by ID) AS Row,ID,aname,aage
from @t)
,T2 as (
select row_number() over (order by ID) AS Row,ID,aname,aage
from @t a where not exists (
select 1 from T1 b where a.ID = b.ID
)
)select *
from T1 LEFT join T2 ON T1.Row = T2.Row
create table T (
ID int not null,
aname varchar(8) not null,
aage tinyint not null
)
insert into T
select 1,'张三',10 union all
select 2,'李四',20 union all
select 3,'王五',30 union all
select 4,'孙六',22 union all
select 5,'赵达',44 union all
select 6,'钱二',21 union all
select 7,'吕宋',35
GO
create view v_T
as
with T1 as (
select top 50 percent
row_number() over (order by ID) AS Row,ID,aname,aage
from T)
,T2 as (
select row_number() over (order by ID) AS Row,ID,aname,aage
from T a where not exists (
select 1 from T1 b where a.ID = b.ID
)
)select t1.ID as ID1,t1.aname as aname1,t1.aage as aage1,
t2.ID as ID2,t2.aname as aname2,t2.aage as aage2
from T1 LEFT join T2 ON T1.Row = T2.Row
1 '张三' '10' 1
2 '李四' '20' 1
3 '王五' '30' 1
4 '孙六' '22' 2
5 '赵达' '44' 3
6 '钱二' '21' 3
7 '吕宋' '35' 4/*
表结构最初如上所示,但是为了排版出报表,客户要求:
只能显示总记录条数的一半记录,
另外一半或者不足一半的记录行转列,排在显示出来的记录后面,如下所示结构。
*/
ID aname1 aage1 aname2 aage2 PID
1 '张三' '10' '李四' '20' 1
3 '王五' '30' NULL NULL 1
4 '赵达' '44' NULL NULL 2
5 '孙六' '22' '钱二' '21' 3
7 '吕宋' '35' NULL NULL 4如果是这样的结果,这个语句要怎么改呢???
麻烦你们了!!!