sql查询语句，化繁为简——求教高手

[小__猪]

有这样一张表table1,有如下三个字段month(月),day（天），value(值)，
每天最多有一个值。

month day value
----------- ----------- -----------
1 1 3
1 2 4
1 3 5
1 4 5
1 5 22
2 1 24
2 2 25
2 3 55
2 5 78
3 2 67
3 3 56
3 4 34

希望能用一句sql查询语句，来实现如下结果：

某天 一月 二月 三月
day month1 month2 month3
----------- ----------- ----------- -----------
1 3 24 NULL
2 4 25 67
3 5 55 56
4 5 NULL 34
5 22 78 NULL

我尝试了一下自连接的方法如下：
select distinct m.day,month1=m1.value,month2=m2.value,month3=m3.value from table1 m left join table1 m1 on m1.day=m.day and m1.month=1 left join table1 m2 on m2.day=m.day and m2.month=2 left join table1 m3 on m3.day=m.day and m3.month=3

虽然也可以得到上述结果，但是我觉得太繁了些，特别当是12个月的话，而且查询效率也不高，如果不用distinct将会有好多重复的记录。

不知道哪位大侠能给我一个简洁一些的表达方法。

[gjqdsdcn]

select day,case month when month then day end from 表 group by day

[gjqdsdcn]

select day,sum(case month when month then day end )from 表 group by day

[yujohny]

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)

[CSDNM]

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

[gjqdsdcn]

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)

[txlicenhe]

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

[gjqdsdcn]

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

[yujohny]

更正一下
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

[webswim]

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

查询效率会比你那个好些

[nboys]

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,34

declare @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

[nboys]

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,34

declare @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