select day,sum(case month when month then day end )from 表 group by day
select [day], (select value from table1 where [day] = a.[day] and [month] =1) AS 一月, (select value from table1 where [day] = a.[day] and [month] =2) AS 二月, (select value from table1 where [day] = a.[day] and [month] =3) AS 三月, (select value from table1 where [day] = a.[day] and [month] =4) AS 四月, (select value from table1 where [day] = a.[day] and [month] =5) AS 五月, (select value from table1 where [day] = a.[day] and [month] =6) AS 六月, (select value from table1 where [day] = a.[day] and [month] =7) AS 七月, (select value from table1 where [day] = a.[day] and [month] =8) AS 八月, (select value from table1 where [day] = a.[day] and [month] =9) AS 九月, (select value from table1 where [day] = a.[day] and [month] =10) AS 十月, (select value from table1 where [day] = a.[day] and [month] =11) AS 十一月, (select value from table1 where [day] = a.[day] and [month] =12) AS 十二月, from a
declare @s varchar(3000) set @s = 'select [day]' select @s = @s + ',sum(case when [month] ='''+ [month] + ''' then value else 0 end) as [' +[month]+'月]' from (select distinct [month] from table1) A order by [month] set @s = @s + ' from talbe1 group by [day]' exec (@s)
select day,SUM(case month when 1 then value end) AS month1, SUM(case month when 2 then value end) AS month2, ... SUM(case month when 12 then value end) AS month12 from 表 group by day
select day,sum(case month when month then value end ) as month from 表 group by day
declare @s varchar(3000) set @s = 'select [day]' select @s = @s + ',sum(case when [month] ='''+ [month] + ''' then value else 0 end) as [' +[month]+'月]' from (select distinct [month] from table1) A order by [month] set @s = @s + ' from talbe1 group by [day]' exec (@s)
select day,case month when 1 then value end as month1, case month when 2 then value end as month2, case month when 3 then value end as month3 from 表 group by day
select day,sum(case month when month then value end ) as month from (select distinct month from table1)order by month from 表 group by day
更正一下 CREATE TABLE #aa(month int,day int,value int) INSERT INTO #aa VALUES(1,1,3) INSERT INTO #aa VALUES(1,2,4) INSERT INTO #aa VALUES(1,3,5) INSERT INTO #aa VALUES(1,4,5) INSERT INTO #aa VALUES(1,5,22) INSERT INTO #aa VALUES(2,1,24) INSERT INTO #aa VALUES(2,2,25) INSERT INTO #aa VALUES(2,3,55) INSERT INTO #aa VALUES(2,5,55) INSERT INTO #aa VALUES(3,2,67) INSERT INTO #aa VALUES(3,3,56) INSERT INTO #aa VALUES(3,4,34) select [day], (select value from #aa where [day] = a.[day] and [month] =1) AS 一月, (select value from #aa where [day] = a.[day] and [month] =2) AS 二月, (select value from #aa where [day] = a.[day] and [month] =3) AS 三月, (select value from #aa where [day] = a.[day] and [month] =4) AS 四月, (select value from #aa where [day] = a.[day] and [month] =5) AS 五月, (select value from #aa where [day] = a.[day] and [month] =6) AS 六月, (select value from #aa where [day] = a.[day] and [month] =7) AS 七月, (select value from #aa where [day] = a.[day] and [month] =8) AS 八月, (select value from #aa where [day] = a.[day] and [month] =9) AS 九月, (select value from #aa where [day] = a.[day] and [month] =10) AS 十月, (select value from #aa where [day] = a.[day] and [month] =11) AS 十一月, (select value from #aa where [day] = a.[day] and [month] =12) AS 十二月 from #aa a GROUP BY [day]drop Table #aa测试结果如下 1 3 24 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2 4 25 67 NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 5 55 56 NULL NULL NULL NULL NULL NULL NULL NULL NULL 4 5 NULL 34 NULL NULL NULL NULL NULL NULL NULL NULL NULL 5 22 55 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
to zhujiang_1977 (朱江) :看看这个怎么样:select d.day, m1.value as month1, m2.value as month2, m3.value as month3 from (select distinct day from table1) d, (select day,value from table1 where month=1) m1, (select day,value from table1 where month=2) m2, (select day,value from table1 where month=3) m3 where d.day *= m1.day and d.day *= m2.day and d.day *= m3.day查询效率会比你那个好些
create table #a (month int,day int,value int) insert into #a select 1,1,3 union all select 1,2,4 union all select 1,3,5 union all select 1,4,5 union all select 1,5,22 union all select 2,1,24. union all select 2,2,25 union all select 2,3,55 union all select 2,5,78 union all select 3,2,67 union all select 3,3,56 union all select 3,4,34declare @i int,@sql varchar(500) set @i=0 set @sql='select day ' select @i=@i+1,@sql=@sql+' ,isnull(sum(case month when '''+cast(@i as varchar)+''' then value end),0) as ['+cast(@i as varchar)+'月]' from (select distinct month from #a) a select @sql=@sql+ ' from #a group by day' exec (@sql) drop table #a
create table #a (month int,day int,value int) insert into #a select 1,1,3 union all select 1,2,4 union all select 1,3,5 union all select 1,4,5 union all select 1,5,22 union all select 2,1,24. union all select 3,2,25 union all select 4,3,55 union all select 5,5,78 union all select 6,2,67 union all select 7,3,56 union all select 8,4,34 union all select 9,4,34 union all select 10,4,34 union all select 11,4,34 union all select 12,4,34declare @i int,@sql varchar(5000) set @i=0 set @sql='select day ' select @i=@i+1,@sql=@sql+' ,isnull(sum(case month when '''+cast(@i as varchar)+''' then value end),0) as [第'+cast(@i as varchar)+'月]' from (select distinct month from #a) a select @sql=@sql+ ' from #a group by day' exec (@sql) drop table #a结果: ------------------------------------- 1 3 24 0 0 0 0 0 0 0 0 0 0 2 4 0 25 0 0 67 0 0 0 0 0 0 3 5 0 0 55 0 0 56 0 0 0 0 0 4 5 0 0 0 0 0 0 34 34 34 34 34 5 22 0 0 0 78 0 0 0 0 0 0 0
(select value from table1 where [day] = a.[day] and [month] =1) AS 一月,
(select value from table1 where [day] = a.[day] and [month] =2) AS 二月,
(select value from table1 where [day] = a.[day] and [month] =3) AS 三月,
(select value from table1 where [day] = a.[day] and [month] =4) AS 四月,
(select value from table1 where [day] = a.[day] and [month] =5) AS 五月,
(select value from table1 where [day] = a.[day] and [month] =6) AS 六月,
(select value from table1 where [day] = a.[day] and [month] =7) AS 七月,
(select value from table1 where [day] = a.[day] and [month] =8) AS 八月,
(select value from table1 where [day] = a.[day] and [month] =9) AS 九月,
(select value from table1 where [day] = a.[day] and [month] =10) AS 十月,
(select value from table1 where [day] = a.[day] and [month] =11) AS 十一月,
(select value from table1 where [day] = a.[day] and [month] =12) AS 十二月,
from a
set @s = 'select [day]'
select @s = @s + ',sum(case when [month] ='''+ [month] + ''' then value else 0 end) as [' +[month]+'月]'
from (select distinct [month] from table1) A order by [month]
set @s = @s + ' from talbe1 group by [day]'
exec (@s)
SUM(case month when 2 then value end) AS month2,
...
SUM(case month when 12 then value end) AS month12
from 表 group by day
set @s = 'select [day]'
select @s = @s + ',sum(case when [month] ='''+ [month] + ''' then value else 0 end) as [' +[month]+'月]'
from (select distinct [month] from table1) A order by [month]
set @s = @s + ' from talbe1 group by [day]'
exec (@s)
case month when 2 then value end as month2,
case month when 3 then value end as month3
from 表 group by day
CREATE TABLE #aa(month int,day int,value int)
INSERT INTO #aa VALUES(1,1,3)
INSERT INTO #aa VALUES(1,2,4)
INSERT INTO #aa VALUES(1,3,5)
INSERT INTO #aa VALUES(1,4,5)
INSERT INTO #aa VALUES(1,5,22)
INSERT INTO #aa VALUES(2,1,24)
INSERT INTO #aa VALUES(2,2,25)
INSERT INTO #aa VALUES(2,3,55)
INSERT INTO #aa VALUES(2,5,55)
INSERT INTO #aa VALUES(3,2,67)
INSERT INTO #aa VALUES(3,3,56)
INSERT INTO #aa VALUES(3,4,34)
select [day],
(select value from #aa where [day] = a.[day] and [month] =1) AS 一月,
(select value from #aa where [day] = a.[day] and [month] =2) AS 二月,
(select value from #aa where [day] = a.[day] and [month] =3) AS 三月,
(select value from #aa where [day] = a.[day] and [month] =4) AS 四月,
(select value from #aa where [day] = a.[day] and [month] =5) AS 五月,
(select value from #aa where [day] = a.[day] and [month] =6) AS 六月,
(select value from #aa where [day] = a.[day] and [month] =7) AS 七月,
(select value from #aa where [day] = a.[day] and [month] =8) AS 八月,
(select value from #aa where [day] = a.[day] and [month] =9) AS 九月,
(select value from #aa where [day] = a.[day] and [month] =10) AS 十月,
(select value from #aa where [day] = a.[day] and [month] =11) AS 十一月,
(select value from #aa where [day] = a.[day] and [month] =12) AS 十二月
from #aa a GROUP BY [day]drop Table #aa测试结果如下
1 3 24 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 4 25 67 NULL NULL NULL NULL NULL NULL NULL NULL NULL
3 5 55 56 NULL NULL NULL NULL NULL NULL NULL NULL NULL
4 5 NULL 34 NULL NULL NULL NULL NULL NULL NULL NULL NULL
5 22 55 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
from
(select distinct day from table1) d,
(select day,value from table1 where month=1) m1,
(select day,value from table1 where month=2) m2,
(select day,value from table1 where month=3) m3
where
d.day *= m1.day
and d.day *= m2.day
and d.day *= m3.day查询效率会比你那个好些
insert into #a
select 1,1,3
union all
select 1,2,4
union all
select 1,3,5
union all
select 1,4,5
union all
select 1,5,22
union all
select 2,1,24.
union all
select 2,2,25
union all
select 2,3,55
union all
select 2,5,78
union all
select 3,2,67
union all
select 3,3,56
union all
select 3,4,34declare @i int,@sql varchar(500)
set @i=0
set @sql='select day '
select @i=@i+1,@sql=@sql+' ,isnull(sum(case month when '''+cast(@i as varchar)+''' then value end),0) as ['+cast(@i as varchar)+'月]'
from (select distinct month from #a) a
select @sql=@sql+ ' from #a group by day'
exec (@sql)
drop table #a
insert into #a
select 1,1,3
union all
select 1,2,4
union all
select 1,3,5
union all
select 1,4,5
union all
select 1,5,22
union all
select 2,1,24.
union all
select 3,2,25
union all
select 4,3,55
union all
select 5,5,78
union all
select 6,2,67
union all
select 7,3,56
union all
select 8,4,34
union all
select 9,4,34
union all
select 10,4,34
union all
select 11,4,34
union all
select 12,4,34declare @i int,@sql varchar(5000)
set @i=0
set @sql='select day '
select @i=@i+1,@sql=@sql+' ,isnull(sum(case month when '''+cast(@i as varchar)+''' then value end),0) as [第'+cast(@i as varchar)+'月]'
from (select distinct month from #a) a
select @sql=@sql+ ' from #a group by day'
exec (@sql)
drop table #a结果:
-------------------------------------
1 3 24 0 0 0 0 0 0 0 0 0 0
2 4 0 25 0 0 67 0 0 0 0 0 0
3 5 0 0 55 0 0 56 0 0 0 0 0
4 5 0 0 0 0 0 0 34 34 34 34 34
5 22 0 0 0 78 0 0 0 0 0 0 0