表如下
地点 日期 温度
AA 2008-1-1 20
BB 2008-1-1 21
AA 2008-1-2 22
BB 2008-1-2 23
我想要用sql生产交叉报表如下:
地点 2008-1-1 2008-1-2
AA 20 22
BB 21 23怎么按照上面的时间顺序生产列 2008-1-1 2008-1-2 ....谢谢
地点 日期 温度
AA 2008-1-1 20
BB 2008-1-1 21
AA 2008-1-2 22
BB 2008-1-2 23
我想要用sql生产交叉报表如下:
地点 2008-1-1 2008-1-2
AA 20 22
BB 21 23怎么按照上面的时间顺序生产列 2008-1-1 2008-1-2 ....谢谢
insert into tb select 'aa','2008-1-1',20
insert into tb select 'bb','2008-1-1',21
insert into tb select 'aa','2008-1-2',22
insert into tb select 'bb','2008-1-2',23select 地点,
max(case 日期 when '2008-1-1' then 温度 end) '2008-1-1',
max(case 日期 when '2008-1-2' then 温度 end) '2008-1-2'
from tb
group by 地点declare @sql varchar(8000)
set @sql = 'select 地点'
select @sql = @sql + ' , max(case 日期 when ''' + convert(varchar(10),日期,120) + ''' then 温度 end) [' + convert(varchar(10),日期,120) + ']'
from (select distinct 日期 from tb) as a
set @sql = @sql + ' from tb group by 地点'
exec(@sql) 地点 2008-01-01 2008-01-02
aa 20 22
bb 21 23
insert t
select 'AA','2008-1-1', 20
union select 'BB','2008-1-1', 21
union select 'AA','2008-1-2', 22
union select 'BB','2008-1-2', 23
declare @str varchar(8000)select @str='select 地点'
select @str=@str+',['+convert(char(10),日期,21)+'=max(case when convert(char(10),日期,21)='''+convert(char(10),日期,21)+''' then 温度 else '''' end) '
from t
select @str=@str+'from t group by 地点'
exec(@str)drop table t
set @sql=''
select @sql=@sql+',max(case when date='''+convert(varchar(10),date)+''' then wd else null end) as '''+convert(varchar(10),date,120)+'''' from (select distinct date from #t)a
select @sql='select dd,'+stuff(@sql,1,1,'')+' from #t group by dd'
exec(@sql)dd 2008-01-01 2008-01-02
---------- ----------- -----------
AA 20 22
BB 21 23
insert t
select 'AA','2008-1-1', 20
union select 'BB','2008-1-1', 21
union select 'AA','2008-1-2', 22
union select 'BB','2008-1-2', 23
union select 'AA','2008-2-2', 22
union select 'BB','2008-2-2', 23 declare @str varchar(8000)select @str='select 地点'
select @str=@str+',['+convert(char(10),日期,21)+']=max(case when convert(char(10),日期,21)='''+convert(char(10),日期,21)+''' then 温度 else '''' end) '
from t
select @str=@str+'from t group by 地点'
print @str
exec(@str)drop table t
这个子语句又不能用order by还有没有什么办法
insert into table_Pqs
select 'AA','2008-1-1',20 union all
select 'BB','2008-1-1',21 union all
select 'AA','2008-1-2',22 union all
select 'BB','2008-1-2',23 select distinct date into #t from table_Pqs order by date
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when date='''+convert(varchar(10),date)+''' then wd else null end) as '''+convert(varchar(10),date,120)+'''' from (select date from #t)a
select @sql='select dd,'+stuff(@sql,1,1,'')+' from table_Pqs group by dd'
exec(@sql)
drop table #t,table_Pqs
dd 2008-01-01 2008-01-02
---------- ----------- -----------
AA 20 22
BB 21 23
create table #t(dd varchar(10),date datetime,wd int)insert into #t values('AA','2008-1-1',20)insert into #t values('BB','2008-1-1',21)insert into #t values('AA','2008-1-2',22)insert into #t values('BB','2008-1-2',23)declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when date='''+convert(varchar(10),date)+''' then wd else null end) as '''+convert(varchar(10),date,120)+'''' from (select distinct top 100 PERCENT date from #t order by date)a
select @sql='select dd,'+stuff(@sql,1,1,'')+' from #t group by dd'
exec(@sql)select distinct top 100 PERCENT date from #t order by datedd 2008-01-01 2008-01-02
---------- ----------- -----------
AA 20 22
BB 21 23
排序:
create table tdd(地点 varchar(10), 日期 datetime, 温度 int)
insert tdd
select 'AA','2008-1-1', 20
union select 'BB','2008-1-1', 21
union select 'AA','2008-1-1', 21
union select 'AA','2008-1-2', 22
union select 'BB','2008-1-2', 23declare @sql varchar(8000)
set @sql='select 地点'
select @sql=@sql+',['+日期+']=max(case 日期 when '''+日期+ ''' then 温度 else 0 end)' from
(select top 100 percent convert(varchar(10),日期,120)日期 from tdd group by 日期 order by 日期)a
set @sql=@sql+' from tdd group by 地点'
exec(@sql)