续上一问：多行的值转换为列显示

Question

上一问链接http://topic.csdn.net/u/20111121/09/f1ff65c0-8b89-4e0b-8c36-6aa78a7cf979.html
可以实现多行值转为列显示，现在遇到一个新的问题，就是出现同一时间有多条数据，想把数据先取平均值后再列显示，请高手指点。
ID NAME TIME      TEM HUM
01 张三 2011-10-11 20 21
02 李四 2011-10-11 19 18
03 张三 2011-10-11 19 20
04 李四 2011-10-11 18 19
05 张三 2011-10-12 22 20
06 李四 2011-10-12 19 15
||
            张三      李四
2011-10-11 19.5(TEM) 18.5(TEM)
2011-10-12  22(TEM)   19(TEM)

Answer 1

里面加个 avg 就可以了。

Answer 2

谢谢，我是这样写的select time,avg(case when name = '张三' then tem else '' end) as '张三',
avg(case when name = '李四' then tem else '' end) as '李四'
from record group by time 
不过查询出来的数据是
time         张三   李四
2011-10-11   19.5   0
2011-10-11   0      18.5
2011-10-12   22     19请问我哪个地方写错了？

Answer 3

select convert(varchar(10),time,120) time,
  avg(case when name = '张三' then tem else 0 end) as '张三',
  avg(case when name = '李四' then tem else 0 end) as '李四'
from record 
group by convert(varchar(10),time,120)
try !

Answer 4

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] nvarchar(2),[NAME] nvarchar(2),[TIME] Datetime,[TEM] int,[HUM] int)
Insert #T
select N'01',N'张三','2011-10-11',20,21 union all
select N'02',N'李四','2011-10-11',19,18 union all
select N'03',N'张三','2011-10-11',19,20 union all
select N'04',N'李四','2011-10-11',18,19 union all
select N'05',N'张三','2011-10-12',22,20 union all
select N'06',N'李四','2011-10-12',19,15
Godeclare @s nvarchar(4000)
set @s=''
Select     @s=@s+N','+quotename(NAME)+N'=max(case when NAME=N'+quotename(NAME,'''')+N' then [TEM] else 0 end)'
from #T group by NAME
--顯示生成語句
print N'select TIME'+@s+N' from (SELECT [TIME],[NAME],cast(AVG([TEM]*1.0) as decimal(18,2)) AS [TEM] FROM #T GROUP BY [NAME],[TIME]) as a group by TIME'exec(N'select TIME'+@s+N' from (SELECT [TIME],[NAME],cast(AVG([TEM]*1.0) as decimal(18,2)) AS [TEM]FROM #T GROUP BY [NAME],[TIME]) as a group by TIME')go/*
TIME 李四 张三
2011-10-11 00:00:00.000 18.50 19.50
2011-10-12 00:00:00.000 19.00 22.00
*/

Answer 5

谢谢，那如果需要把时分秒也给算进去，我把它改成convert(datetime,time,120)，这样对吗，我试验了下，平均值不正确...

Answer 6

declare @sql varchar(8000)
set @sql = 'select CONVERT(varchar(10),time,120) as time '
select @sql = @sql + ' , avg(case name when ''' + name + ''' then  ltrim(tem)+''(tem)'' else '''' end) [' + name + ']'
from (select distinct name from tb) as a
set @sql = @sql + ' from tb group by CONVERT(varchar(10),time,120)'
exec(@sql) 

Answer 7

时分秒就不对了，把convert中的varchar(10)改为varchar(19)试试。

Answer 8

if object_id('record','U') is not null
   drop table record
go
CREATE TABLE [dbo].[record](
    [id] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [time] [datetime] NULL,
    [temperature] [float] NULL,
    [humidity] [float] NULL
)
go
insert into record
select '01','张三','2011-10-11',20,21 union all
select '02','李四','2011-10-11',19,18 union all
select '03','张三','2011-10-11',19,20 union all
select '04','李四','2011-10-11',18,19 union all
select '05','张三','2011-10-12',22,20 union all
select '06','李四','2011-10-12',19,15
go
--方法一固定列
select TIME,
       [张三]=CAST(SUM(CASE WHEN NAME='张三' then temperature  else 0 END)*1.0/SUM(CASE WHEN NAME='张三' then 1  else 0 END) AS VARCHAR)+'(TEM)',
       [李四]=CAST(SUM(CASE WHEN NAME='李四' then temperature  else 0 END)*1.0/SUM(CASE WHEN NAME='李四' then 1  else 0 END) AS VARCHAR)+'(TEM)'
FROM record group by TIME
GO
--方法二不固定列
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+'['+Name+']=CAST(SUM(CASE WHEN NAME='''+NAME+''' THEN temperature else 0 END)*1.0/SUM(CASE WHEN NAME='''+Name+''' then 1 else 0 end) AS VARCHAR)+''(TEM)''' FROM (select distinct Name from  record)a
exec('select Time,'+@sql+' from record group by time')
/*
Time                    李四                                  张三
----------------------- ----------------------------------- -----------------------------------
2011-10-11 00:00:00.000 18.5(TEM)                           19.5(TEM)
2011-10-12 00:00:00.000 19(TEM)                             22(TEM)(2 行受影响)*/楼主要的不是avg，得用sum的值除以记录数

Answer 9

有時分秒時這樣用--顯示生成語句這一段可以看到生成的語句use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] nvarchar(2),[NAME] nvarchar(2),[TIME] Datetime,[TEM] int,[HUM] int)
Insert #T
select N'01',N'张三','2011-10-11',20,21 union all
select N'02',N'李四','2011-10-11',19,18 union all
select N'03',N'张三','2011-10-11',19,20 union all
select N'04',N'李四','2011-10-11',18,19 union all
select N'05',N'张三','2011-10-12',22,20 union all
select N'06',N'李四','2011-10-12',19,15
Godeclare @s nvarchar(4000)
set @s=''
Select     @s=@s+N','+quotename(NAME)+N'=max(case when NAME=N'+quotename(NAME,'''')+N' then [TEM] else 0 end)'
from #T group by NAME
--顯示生成語句
print N'select TIME'+@s+N' from (SELECT [TIME]=convert(varchar(10),TIME,120),[NAME],cast(AVG([TEM]*1.0) as decimal(18,2)) AS [TEM] FROM #T GROUP BY [NAME],[TIME]) as a group by TIME'exec(N'select TIME'+@s+N' from (SELECT [TIME]=convert(varchar(10),TIME,120),[NAME],cast(AVG([TEM]*1.0) as decimal(18,2)) AS [TEM]FROM #T GROUP BY [NAME],[TIME]) as a group by TIME')GO
/*
TIME 李四 张三
2011-10-11 18.50 19.50
2011-10-12 19.00 22.00
*/

Answer 10

谢谢各位，解决了，把解决方法放出来，写的不好请多包涵：
select time,avg(case name when '张三' then tem end) as '张三',
avg(case name when '李四' then tem end) as '李四'
from record group by time order by time asc