22,209
社区成员
发帖
与我相关
我的任务
分享
SELECT
MAX(CASE WHEN a/8%2=0 THEN COL END)COL1
,MAX(CASE WHEN a/8%2=1 THEN COL END)COL2
FROM(SELECT(ROW_NUMBER()OVER(ORDER BY GETDATE()))-1 a,COL FROM test)T
GROUP BY a/16,a%8
ORDER BY a/16,a%8
你参考一下,假设数据列名为COL
另外,行转列的效率可想而知,与其在数据库行转列,不如在页面上进行遍历,那是程序的强项
数据库只需要查询每页总共要给多少数据就行了
declare @x int
set @x=4 --楼主是 8
;with t as (
select fid/@x fp,fid %@x fm,fid,fv from (
select (ROW_NUMBER() over(order by fv)) fid,fv from (
select fa,cast(newid() as varchar(100)) fv
from dbo.MakeTable100(1,1000) --这里使用一个表函数 生成1000条记录
) a
) a
)
select t0.fv+t1.fv f1,t2.fv+t3.fv f2 from t t0
left join t t1 on t1.fm=1 and t1.fp=t0.fp
left join t t2 on t2.fm=2 and t2.fp=t0.fp
left join t t3 on t3.fm=3 and t3.fp=t0.fp
where t0.fm=0