select distinct id,
M1=(select [money] from #t where nid=1),
M2=(select [money] from #t where nid=2),
M3=(select [money] from #t where nid=3),
M4=(select [money] from #t where nid=4)
from #t
M1=(select [money] from #t where nid=1),
M2=(select [money] from #t where nid=2),
M3=(select [money] from #t where nid=3),
M4=(select [money] from #t where nid=4)
from #t
解决方案 »
- 求1sql
- 如何判断某表已经存在数据库中
- 我想把不满5位的数字在前面用0补足,比如1转化成0001,2转化成0002。用asp或者sql函数该怎么写
- 急急急~求救:对于多个基表不支持动态 SQL 生成
- 有牛人没,看看这个触发器为什么错误
- 远程连接的怪事,搞了五天了,本人差不多进精神病院了,555555
- 请教一个简单的SQLSERVER问题.
- 怎样打开sql server2000的1433端口
- id 值一般用什么实现的 自增+indentity??
- 请看一下一个查询的问题
- 谁看到钻石用户的钻石是什么样子?
- 在win xp平台上可安装sql server的哪个版本?个人/标准/企业/开发,这四个版本,哪一个可以?谢!
select distinct id,
M1=(select [money] from #t where nid=1),
M2=(select [money] from #t where nid=2),
M3=(select [money] from #t where nid=3),
M4=(select [money] from #t where nid=4)
from #t
(
ID varchar(5),
Money int
)insert A select '002',1200
insert A select '002',23
insert A select '002',5632
insert A select '002',213
select identity(int,1,1) as T_id,* into #T from Aselect * from #Tselect ID,
max(case when T_ID=1 then Money else 0 end ) as M1,
max(case when T_ID=2 then Money else 0 end ) as M2,
max(case when T_ID=3 then Money else 0 end ) as M3,
max(case when T_ID=4 then Money else 0 end ) as M4
from #T
group by ID
(
ID varchar(5),
Money int
)insert A select '002',1200
insert A select '002',23
insert A select '002',5632
insert A select '002',213
insert A select '003',11
insert A select '003',33
insert A select '003',444
drop table #T
select identity(int,1,1) as T_id,* into #T from Aselect * from #Tselect A.ID,
max(case when A.T_ID=1 then A.Money else 0 end ) as M1,
max(case when A.T_ID=2 then A.Money else 0 end ) as M2,
max(case when A.T_ID=3 then A.Money else 0 end ) as M3,
max(case when A.T_ID=4 then A.Money else 0 end ) as M4
from (select (select count(*) from #T where T_id<=T.T_id and Id=T.id) T_ID,T.ID,T.Money from #T T) A
group by A.ID
set @T_SQL=''
select @T_SQL=@T_SQL + 'max(case when A.T_ID=' + cast(T_ID as varchar) + 'then A.Money else 0 end ) as M'+ cast(T_ID as varchar) +',' from (select distinct T_ID from(select (select count(*) from #T where T_id<=T.T_id and Id=T.id) T_ID,T.ID,T.Money from #T T) M) B
set @T_SQL='select A.ID,' + left(@T_SQL,len(@T_SQL)-1) + ' from (select (select count(*) from #T where T_id<=T.T_id and Id=T.id) T_ID,T.ID,T.Money from #T T) A group by A.ID'
exec (@T_SQL)
select nid=identity(int,1,1),* into #t from yourtable where id='002'
select @s='',@i=count(*) from #t
while @i>0
begin
select @s=',[M'+cast(@i as varchar(10))+']=(select [money] from #t where nid='+cast(@i as varchar(10))+')'+@s,@i=@i-1
end
exec('select distinct id'+@s+' from #t')
是就给个方法不是就别山呼
一个个的转的
我还不知道,
按你说的 我还不如用 select *
把它取出来在内存里来转那,还节省SQL server 的资源