select ReportDate,TagName, max(case RangeID when 1 then LastTime_TotalSeconds else 0 end) as 1, max(case RangeID when 2 then LastTime_TotalSeconds else 0 end) as 2, max(case RangeID when 3 then LastTime_TotalSeconds else 0 end) as 3, . . . from tb group by TagName,ReportDate
declare @sql varchar(8000) select @sql=isnull(@sql+',','')+' max(case RangeID when '''+RangeID+''' then LastTime_TotalSeconds else 0 end) ['+RangeID+']' from (select distinct RangeID from tb)as a set @sql='select ReportDate,TagName,'+@sql+' from tb group by TagName,ReportDate' exec(@sql)
--SQL 2005 以上版本 declare @sql varchar(8000) set @sql='' --初始化变量@sql select @sql=@sql+','+ RangeID from tb group by RangeID --变量多值赋值 set @sql=stuff(@sql,1,1,'')--去掉首个',' set @sql='select * from tb pivot (max(LastTime_TotalSeconds) for RangeID in ('+@sql+'))a' exec(@sql)
select ReportDate,TagName, max(case RangeID when 1 then LastTime_TotalSeconds else 0 end) as '1', max(case RangeID when 2 then LastTime_TotalSeconds else 0 end) as '2', . . . from tb group by TagName,ReportDate 字符
max(case RangeID when 1 then LastTime_TotalSeconds else 0 end) as 1,
max(case RangeID when 2 then LastTime_TotalSeconds else 0 end) as 2,
max(case RangeID when 3 then LastTime_TotalSeconds else 0 end) as 3,
.
.
.
from tb
group by TagName,ReportDate
select @sql=isnull(@sql+',','')+' max(case RangeID when '''+RangeID+''' then LastTime_TotalSeconds else 0 end) ['+RangeID+']' from (select distinct RangeID from tb)as a
set @sql='select ReportDate,TagName,'+@sql+' from tb group by TagName,ReportDate'
exec(@sql)
declare @sql varchar(8000)
set @sql='' --初始化变量@sql
select @sql=@sql+','+ RangeID from tb group by RangeID --变量多值赋值
set @sql=stuff(@sql,1,1,'')--去掉首个','
set @sql='select * from tb pivot (max(LastTime_TotalSeconds) for RangeID in ('+@sql+'))a'
exec(@sql)
CREATE TABLE [dbo].[RmisEvo_CompareCensus](
[id] [int] IDENTITY(1,1) NOT NULL,
[ReportDate] [datetime] NULL,
[TagName] [nvarchar](50) NULL,
[RangeID] [int] NULL,
[LastTime_TotalSeconds] [decimal](18, 3) NULL
) ON [PRIMARY]
max(case RangeID when 1 then LastTime_TotalSeconds else 0 end) as '1',
max(case RangeID when 2 then LastTime_TotalSeconds else 0 end) as '2',
.
.
.
from tb
group by TagName,ReportDate
字符