a1,b1,a2,b2,a3,b3,a4,b4,a5,b5
-----------------------------
1,b-1,2,b-2,3,b-3,4,b-4,5,b-5
6,b-6,7,b-7这个结果没看懂....
-----------------------------
1,b-1,2,b-2,3,b-3,4,b-4,5,b-5
6,b-6,7,b-7这个结果没看懂....
解决方案 »
- 【优化】求助!!!查询数据问题,最好不要用游标
- Sqlserver 高级查询
- 这个sql语句对不对
- 关于apply不是很懂
- SELECT SUM(IF (DATEDIFF(n, server_date,server_finish_date) > 240 THEN ww = 240 ELSE ww = (DATEDIFF(n, server_date,server_finish_
- SQL组合查询的问题
- 这是神马情况啊?换了系统{xp-win7}后就这样额……
- 库存管理报表查询
- sql server进不了了,怎么办啊
- 请问 close database 是不是也可起到 commit work 的作用? 就是说只用 close database 而不用 commit work 可以完成对表的修改吗?
- 一个关于事务并发的问题,银行取钱问题
- 求一个SQL行列转换的解决方案
select number*5+1 a1,'b-'+CAST(number*5+1 as varchar) b1,
number*5+2 a2,'b-'+CAST(number*5+2 as varchar) b2,
number*5+3 a3,'b-'+CAST(number*5+3 as varchar) b3,
number*5+4 a4,'b-'+CAST(number*5+4 as varchar) b4,
number*5+5 a5,'b-'+CAST(number*5+5 as varchar) b5
from master..spt_values t
where t.type='P' and number >= 0
and number <= 10
/*
a1 b1 a2 b2 a3 b3 a4 b4 a5 b5
1 b-1 2 b-2 3 b-3 4 b-4 5 b-5
6 b-6 7 b-7 8 b-8 9 b-9 10 b-10
11 b-11 12 b-12 13 b-13 14 b-14 15 b-15
16 b-16 17 b-17 18 b-18 19 b-19 20 b-20
21 b-21 22 b-22 23 b-23 24 b-24 25 b-25
26 b-26 27 b-27 28 b-28 29 b-29 30 b-30
31 b-31 32 b-32 33 b-33 34 b-34 35 b-35
36 b-36 37 b-37 38 b-38 39 b-39 40 b-40
41 b-41 42 b-42 43 b-43 44 b-44 45 b-45
46 b-46 47 b-47 48 b-48 49 b-49 50 b-50
51 b-51 52 b-52 53 b-53 54 b-54 55 b-55
*/
select rn=row_number() over (partition by rw order by a),* into #A from
(select rw=(row_number() over (order by a)-1)/5,* from #TestB) aselect * from #Adeclare @s varchar(max)
select @s=isnull(@s+',','')+quotename('a'+convert(varchar,rn))+'=max(case when [rn]='+quotename([rn],'''')+'then [a] else null end),'
+quotename('b'+convert(varchar,rn))+'=max(case when [rn]='+quotename([rn],'''')+'then [b] else null end)'
from #A group by rn
set @s='select'+@s+' from #A group by rw'
--print @s
exec(@s)