例如name | current | time
battery1 | 1.5 | 2011-3-24 13:42:02
battery2 | 1.6 | 2011-3-24 13:42:02
battery3 | 1.5 | 2011-3-24 13:42:02
battery4 | 1.3 | 2011-3-24 13:42:02
battery1 | 1.2 | 2011-3-24 13:42:35
battery2 | 1.4 | 2011-3-24 13:42:35
battery3 | 1.5 | 2011-3-24 13:42:35
battery4 | 1.6 | 2011-3-24 13:42:35求SQL 显示如下name 2011-3-24 13:42:02 2011-3-24 13:42:35
battery1 1.5 1.2
battery2 1.6 1.4
battery3 1.5 1.5
battery4 1.3 1.6数据是不断的增加的,列也是不断的增加
battery1 | 1.5 | 2011-3-24 13:42:02
battery2 | 1.6 | 2011-3-24 13:42:02
battery3 | 1.5 | 2011-3-24 13:42:02
battery4 | 1.3 | 2011-3-24 13:42:02
battery1 | 1.2 | 2011-3-24 13:42:35
battery2 | 1.4 | 2011-3-24 13:42:35
battery3 | 1.5 | 2011-3-24 13:42:35
battery4 | 1.6 | 2011-3-24 13:42:35求SQL 显示如下name 2011-3-24 13:42:02 2011-3-24 13:42:35
battery1 1.5 1.2
battery2 1.6 1.4
battery3 1.5 1.5
battery4 1.3 1.6数据是不断的增加的,列也是不断的增加
max(case when rowid='+ltrim(rowid)+' then 行业 else '''' end) as [行业'+ltrim(rowid)+']
红色部分如果是decimal类型数据就报下面错误
从数据类型 varchar 转换为 numeric 时出错如果是varchar类型就可以
如何数据作为列名来显示?
---如果是 DEC数据 后面为 0
create table #TryTB([name] nvarchar(20), [current] float , [time] datetime)
insert #TryTB select 'battery1' , 1.5 , '2011-3-24 13:42:02' union all
select 'battery2' , 1.6 , '2011-3-24 13:42:02' union all
select 'battery3' , 1.5 , '2011-3-24 13:42:02' union all
select 'battery4' , 1.3 , '2011-3-24 13:42:02' union all
select 'battery1' , 1.2 , '2011-3-24 13:42:35' union all
select 'battery2' , 1.4 , '2011-3-24 13:42:35' union all
select 'battery3' , 1.5 , '2011-3-24 13:42:35' union all
select 'battery4' , 1.6 , '2011-3-24 13:42:35'declare @sql as nvarchar(4000)
set @sql=''
select @sql=@sql+',max(case [time] when '''+convert(nvarchar(20),[time],120)+
''' then [current] else 0 end) as '''+convert(nvarchar(20),[time],120)+''''
from #TryTB group by [time]
set @sql='select [name]'+@sql+' from #TryTB group by [name]'
exec(@sql)