日期 站号 实况
20111005 1 21.1
20111006 1 21.2
20111007 1 21.3
20111008 1 21.4
20111009 1 21.5
20111010 1 21.6
20111005 2 23.1
20111006 2 23.2
20111007 2 23.3
20111008 2 23.4
20111009 2 23.5
20111010 2 23.6
.....现想处理成这样的表，如何写SQL？日期 站号 第1天实况 第2天实况 第3天实况 第4天实况 第5天实况
20111005 1 21.1 21.2 21.3 21.4 21.5
20111006 1 21.2 21.3 21.4 21.5 21.6
20111005 2 23.1 23.2 23.3 23.4 23.5
20111006 2 23.2 23.3 23.4 23.5 23.6
.....
20111005 1 21.1
20111006 1 21.2
20111007 1 21.3
20111008 1 21.4
20111009 1 21.5
20111010 1 21.6
20111005 2 23.1
20111006 2 23.2
20111007 2 23.3
20111008 2 23.4
20111009 2 23.5
20111010 2 23.6
.....现想处理成这样的表，如何写SQL？日期 站号 第1天实况 第2天实况 第3天实况 第4天实况 第5天实况
20111005 1 21.1 21.2 21.3 21.4 21.5
20111006 1 21.2 21.3 21.4 21.5 21.6
20111005 2 23.1 23.2 23.3 23.4 23.5
20111006 2 23.2 23.3 23.4 23.5 23.6
.....
---------- ---------- -----------张三 语文 74张三 数学 83张三 物理 93李四 语文 74李四 数学 84李四 物理 94 2、使用SQL Server 2000静态SQL--cselect姓名, max(case课程when'语文'then分数else0end)语文, max(case课程when'数学'then分数else0end)数学, max(case课程when'物理'then分数else0end)物理fromtb
groupby姓名姓名 语文 数学 物理---------- ----------- ----------- -----------李四 74 84 94张三 74 83 93 3、使用SQL Server 2000动态SQL--SQL SERVER 2000动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)--变量按sql语言顺序赋值declare@sqlvarchar(500)set@sql='select姓名'select@sql=@sql+',max(case课程when '''+课程+''' then分数else 0 end)['+课程+']'from(selectdistinct课程fromtb)a--同from tb group by课程，默认按课程名排序set@sql=@sql+' from tb group by姓名'exec(@sql) --使用isnull(),变量先确定动态部分declare@sqlvarchar(8000)select@sql=isnull(@sql+',','')+' max(case课程when '''+课程+''' then分数else 0 end) ['+课程+']'from(selectdistinct课程fromtb)asa set@sql='select姓名,'+@sql+' from tb group by姓名'exec(@sql)
姓名 数学 物理 语文---------- ----------- ----------- -----------李四 84 94 74张三 83 93 74 4、使用SQL Server 2005静态SQLselect*fromtb pivot(max(分数)for课程in(语文,数学,物理))a 5、使用SQL Server 2005动态SQL--使用stuff()declare@sqlvarchar(8000)set@sql='' --初始化变量@sqlselect@sql=@sql+','+课程fromtbgroupby课程--变量多值赋值set@sql=stuff(@sql,1,1,'')--去掉首个','set@sql='select * from tb pivot (max(分数) for课程in ('+@sql+'))a'
exec(@sql) --或使用isnull()declare@sqlvarchar(8000)–-获得课程集合select@sql=isnull(@sql+',','')+课程fromtbgroupby课程 set@sql='select * from tb pivot (max(分数) for课程in ('+@sql+'))a'exec(@sql)
go
create table a
(
date1 datetime,
stopNum int,
state decimal(18,1)
)
go
insert into aselect '20111005',1,21.1 union all
select '20111006',1,21.2 union all
select '20111007',1,21.3 union all
select '20111008',1,21.4 union all
select '20111009',1,21.5 union all
select '20111010',1,21.6 union all
select '20111005',2,23.1 union all
select '20111006',2,23.2 union all
select '20111007',2,23.3 union all
select '20111008',2,23.4 union all
select '20111009',2,23.5 union all
select '20111010',2,23.6
--动态
declare @sql varchar(8000)set @sql = ''
select @sql = @sql+','+'max(case when [date1]= '+quotename('date1')+' then '+quotename('state')+' else 0 end) as ''第'+right(convert(varchar(10),date1,12),1)+'天实况'+'('+replace(convert(varchar(10),date1,121),'-','')+')''' from a group by date1 exec ('select stopNum '+@sql+' from a group by stopNum')
--静态
select stopNum ,max(case when [date1]= [date1] then [state] else 0 end) as '第5天实况(20111005)',
max(case when [date1]= [date1] then [state] else 0 end) as '第6天实况(20111006)',
max(case when [date1]= [date1] then [state] else 0 end) as '第7天实况(20111007)',
max(case when [date1]= [date1] then [state] else 0 end) as '第8天实况(20111008)',
max(case when [date1]= [date1] then [state] else 0 end) as '第9天实况(20111009)',
max(case when [date1]= [date1] then [state] else 0 end) as '第0天实况(20111010)'
from a group by stopNum/*
stopNum 第5天实况(20111005) 第6天实况(20111006) 第7天实况(20111007) 第8天实况(20111008) 第9天实况(20111009) 第0天实况(20111010)
----------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1 21.6 21.6 21.6 21.6 21.6 21.6
2 23.6 23.6 23.6 23.6 23.6 23.6(2 行受影响)*/