日期:2014-05-18  浏览次数:20456 次

为排版进行行转列,谢谢您的帮助
SQL code



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



诚向各位高手请教,谢谢您的帮助!!!!

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

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