我在说明下数据库表和字段这样的
FIXDATA 表
SAMPLING_DATETIME INFECTANT_ID SITE_ID ACTUAL_VALUE
2005.02.04 9:00:00 011 5001 56
2005.02.04 9:00:00 012 5001 57
2005.02.04 9:00:00 013 5001 58
2005.02.04 9:00:00 014 5001 59
T_DIC_SUBSITE表
SITE_NAME SITE_ID
水库 5001
想得到这样的结果
SAMPLING_DATETIME SITE_NAME ACTUAL_VALUE1 ACTUAL_VALUE2 ACTUAL_VALUE3 ACTUAL_VALUE4 SITE_ID
2005.02.04 9:00:00 水库 56 57 58 59 5001
FIXDATA 表
SAMPLING_DATETIME INFECTANT_ID SITE_ID ACTUAL_VALUE
2005.02.04 9:00:00 011 5001 56
2005.02.04 9:00:00 012 5001 57
2005.02.04 9:00:00 013 5001 58
2005.02.04 9:00:00 014 5001 59
T_DIC_SUBSITE表
SITE_NAME SITE_ID
水库 5001
想得到这样的结果
SAMPLING_DATETIME SITE_NAME ACTUAL_VALUE1 ACTUAL_VALUE2 ACTUAL_VALUE3 ACTUAL_VALUE4 SITE_ID
2005.02.04 9:00:00 水库 56 57 58 59 5001
insert FIXDATA
select '2005.02.04 9:00:00' , '011' , '5001' , 56
union all select '2005.02.04 9:00:00' , '012', '5001', 57
union all select '2005.02.04 9:00:00' , '013' , '5001' , 58
union all select '2005.02.04 9:00:00' , '014' , '5001' , 59
union all select '2005.02.04 9:00:00' , '011' , '5002' , 10
union all select '2005.02.04 9:00:00' , '012' , '5002' , 20
union all select '2005.02.04 9:00:00' , '013' , '5002' , 30
union all select '2005.02.04 9:00:00' , '014' , '5002' , 40Create Table T_DIC_SUBSITE(SITE_NAME nvarchar(10),SITE_ID varchar(15))
insert T_DIC_SUBSITE
select '水库', '5001'
union all select 'test','5002'
select *,id=cast(0 as int) into #xx from FIXDATA order by SITE_ID
declare @SITE_ID varchar(10),@ID int
update #xx
set ID=@ID,@ID=(case when SITE_ID=@SITE_ID then @ID+1 else 1 end),@SITE_ID=SITE_ID
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql+',sum(Case ID when ''' + cast(ID as varchar(10))+ ''' then ACTUAL_VALUE else 0 end)[ACTUAL_VALUE'+cast(ID as varchar(10))+']' from (select distinct ID from #xx) as c
set @sql = 'select a.SAMPLING_DATETIME,b.SITE_NAME' + @sql + ',a.SITE_ID from #xx a join T_DIC_SUBSITE b on a.SITE_ID=b.SITE_ID group by a.SAMPLING_DATETIME,b.SITE_NAME,a.SITE_ID'
exec (@sql)
我希望得到的是
SELECT TOP 9 FIXDATA.SAMPLING_DATETIME, T_DIC_SUBSITE.SITE_NAME,
FIXDATA.INFECTANT_ID, AVG(FIXDATA.ACTUAL_VALUE) AS Expr1,
FIXDATA.SITE_ID
FROM FIXDATA INNER JOIN
T_DIC_SUBSITE ON FIXDATA.SITE_ID = T_DIC_SUBSITE.SITE_ID
WHERE (T_DIC_SUBSITE.SITE_NAME = '水库')
GROUP BY FIXDATA.SAMPLING_DATETIME, T_DIC_SUBSITE.SITE_NAME,
FIXDATA.INFECTANT_ID, FIXDATA.SITE_ID
这样的语句