解决方案 »
- 望高手给一SQl语句,万分感谢!!
- 各位前辈问个MSSQL2005的数据镜象问题
- 数据库实例是什么东西?
- 调用存储过程出错
- SQL SERVER的维护计划和作业的区别
- 问个数据更新的问题
- 请教如何用一个SQL语句实现将一个表中的一条记录中的部分字段更新另一条记录的相应的字段
- to pengdali: 结 select [from n to m] from table的帖子。
- 《程序员大本营3.0》数据库版征稿活动
- 如何将sql server中的数据导入oracle中?
- 数据库中表table ,tid是外键,其中有'01’,'02’...等值。删除掉非01,02关联的所有值。
- 如何创建另建现有数据库
SUM(CASE WHEN date = '2010-01-01' THEN [state] ELSE 0 END),
SUM(CASE WHEN date = '2010-01-02' THEN [state] ELSE 0 END),
SUM(CASE WHEN date = '2010-01-03' THEN [state] ELSE 0 END)
FROM #stuct
GROUP BY id
(id int,
date datetime,
state varchar(4)
)insert into #stuct
select 1,'2010-1-1',1 union all
select 1,'2010-1-2',0 union all
select 1,'2010-1-3',1 union all
select 2,'2010-1-1',0 union all
select 2,'2010-1-2',0 union all
select 2,'2010-1-3',1
declare @sql varchar(8000)
select @sql = isnull(@sql+',','')+'max(case when [date]='''+convert(varchar(20),[date],120)+''' then [state] else 0 end) as ['+convert(varchar(20),[date],120)+']'
from (
select distinct [date] from #stuct t
) r
select @sql = '
select id,'+@sql+'
from #stuct
group by id'
exec(@sql)
create table #stuct
(id int,
date datetime,
state varchar(4)
)insert into #stuct
select 1,'2010-1-1',1 union all
select 1,'2010-1-2',0 union all
select 1,'2010-1-3',1 union all
select 2,'2010-1-1',0 union all
select 2,'2010-1-2',0 union all
select 2,'2010-1-3',1
godeclare @sql varchar(4000)
set @sql = 'select id'
select @sql = @sql + ',max(case convert(varchar(10),date,120) when '''
+ date +''' then state end)['+ date +']'
from (select distinct date from (select convert(varchar(10),date,120)date from #stuct)t)e
select @sql = @sql + ' from #stuct group by id'
exec (@sql)drop table #stuctid 2010-01-01 2010-01-02 2010-01-03
----------- ---------- ---------- ----------
1 1 0 1
2 0 0 1(2 行受影响)
select @sql = isnull(@sql+',','')+'max(case when [date]='''+convert(varchar(10),[date],120)+''' then [state] else 0 end) as ['+convert(varchar(10),[date],120)+']'
from (
select distinct [date] from #stuct t
) r
select @sql = '
select id,'+@sql+'
from #stuct
group by id'
exec(@sql)
declare @sql nvarchar(4000)
set @sql = N''
select @sql = @sql + N'[' + date + N'],' from (select distinct date = convert(varchar(10), date, 121) from #stuct) a
set @sql = N'select * from #stuct pivot (max([state]) for date in (' + stuff(@sql, len(@sql), 1, N'') + N')) b'
exec(@sql)
为什么要用sum 对sum 不明白