--示例--示例数据
create table table1(年 int,月 int,日 int,天气现象 varchar(100))
insert table1 select 2000,1,1, '01'
union all select 2000,1,2, '01 10 42'
union all select 2000,1,3, '01'
union all select 2000,1,4, '01 10 42'
union all select 2000,1,5, '01'
union all select 2000,1,6, '01'
union all select 2000,1,7, '01'
union all select 2000,1,8, '01'
union all select 2000,1,23,'01 60'
union all select 2000,1,24,'01 60'
union all select 2000,1,25,'60 80'
union all select 2000,1,26,'60'
union all select 2000,2,7, '01 10 42'
union all select 2000,2,8, '01'
union all select 2000,2,9, '01 60'
union all select 2000,2,10,'01'
union all select 2000,2,11,'01'
union all select 2000,2,12,'01'
union all select 2000,2,13,'01'
union all select 2000,2,14,'01'
go--查询
select top 8000 id=identity(int) into # from syscolumns a,syscolumns b
declare @s nvarchar(4000)
set @s=''
select @s=@s
+','+quotename(a)
+'=sum(case when charindex('
+quotename(' '+a+' ','''')
+','' ''+天气现象+'' '')>0 then 1 else 0 end)'
from(
select distinct a=substring(a.天气现象,b.id,charindex(' ',a.天气现象+' ',b.id)-b.id)
from table1 a,# b
where len(a.天气现象)>=b.id
and charindex(' ',' '+a.天气现象,b.id)=b.id)a
exec('select 年,月'+@s+' from table1 group by 年,月')
drop table #
go--删除测试
drop table table1/*--测试结果
年 月 01 10 42 60 80
----------- ---- ---- ---- ---- ---- ----
2000 1 10 2 2 4 1
2000 2 8 1 1 1 0
--*/
create table table1(年 int,月 int,日 int,天气现象 varchar(100))
insert table1 select 2000,1,1, '01'
union all select 2000,1,2, '01 10 42'
union all select 2000,1,3, '01'
union all select 2000,1,4, '01 10 42'
union all select 2000,1,5, '01'
union all select 2000,1,6, '01'
union all select 2000,1,7, '01'
union all select 2000,1,8, '01'
union all select 2000,1,23,'01 60'
union all select 2000,1,24,'01 60'
union all select 2000,1,25,'60 80'
union all select 2000,1,26,'60'
union all select 2000,2,7, '01 10 42'
union all select 2000,2,8, '01'
union all select 2000,2,9, '01 60'
union all select 2000,2,10,'01'
union all select 2000,2,11,'01'
union all select 2000,2,12,'01'
union all select 2000,2,13,'01'
union all select 2000,2,14,'01'
go--查询
select top 8000 id=identity(int) into # from syscolumns a,syscolumns b
declare @s nvarchar(4000)
set @s=''
select @s=@s
+','+quotename(a)
+'=sum(case when charindex('
+quotename(' '+a+' ','''')
+','' ''+天气现象+'' '')>0 then 1 else 0 end)'
from(
select distinct a=substring(a.天气现象,b.id,charindex(' ',a.天气现象+' ',b.id)-b.id)
from table1 a,# b
where len(a.天气现象)>=b.id
and charindex(' ',' '+a.天气现象,b.id)=b.id)a
exec('select 年,月'+@s+' from table1 group by 年,月')
drop table #
go--删除测试
drop table table1/*--测试结果
年 月 01 10 42 60 80
----------- ---- ---- ---- ---- ---- ----
2000 1 10 2 2 4 1
2000 2 8 1 1 1 0
--*/
----------- ---- ---- ---- ---- ---- ----
2000 1 9 2 2 4 1
2000 2 8 1 1 1 0
年 月 01 10 42 60 80
----------- ---- ---- ---- ---- ---- ----
2000 1 10 3 3 5 1
2000 2 8 1 1 1 0