--不是很理解,统计这四个日期字段自己对应的数据 --这样? select [dateAndTime] from dateAndTime d left join table1 tb on d.pz = tb.pz
select itemCode ,max(case when convert(varchar(10),dateAndTime,120) = '2011-08-14' then [pz] end) as [pz(08-14)] ,max(case when convert(varchar(10),[date_rk],120) = '2011-08-14' then [rk] end) as [rk(08-14)] ,max(case when convert(varchar(10),[date_sg],120) = '2011-08-14' then [sg] end) as [sg(08-14)] ,max(case when convert(varchar(10),[date_xg],120) = '2011-08-14' then [xg] end) as [xg(08-14)] ... ,max(case when convert(varchar(10),[date_xg],120) = '2011-08-17' then [xg] end) as [xg(08-17)] from tab group by itemCode
select itemCode ,max(case when convert(varchar(10),dateAndTime,120) = '2011-08-14' then [pz] end) as [pz(08-14)] ,max(case when convert(varchar(10),[date_rk],120) = '2011-08-14' then [rk] end) as [rk(08-14)] ,max(case when convert(varchar(10),[date_sg],120) = '2011-08-14' then [sg] end) as [sg(08-14)] ,max(case when convert(varchar(10),[date_xg],120) = '2011-08-14' then [xg] end) as [xg(08-14)] ... ,max(case when convert(varchar(10),[date_xg],120) = '2011-08-17' then [xg] end) as [xg(08-17)] from tab group by itemCode
select itemCode ,max(case when convert(varchar(10),dateAndTime,120) = '2011-08-14' then [pz] end) as [pz(08-14)] ,max(case when convert(varchar(10),[date_rk],120) = '2011-08-14' then [rk] end) as [rk(08-14)] ,max(case when convert(varchar(10),[date_sg],120) = '2011-08-14' then [sg] end) as [sg(08-14)] ,max(case when convert(varchar(10),[date_xg],120) = '2011-08-14' then [xg] end) as [xg(08-14)] ... ,max(case when convert(varchar(10),[date_xg],120) = '2011-08-17' then [xg] end) as [xg(08-17)] from tab group by itemCode
select itemCode ,max(case when convert(varchar(10),dateAndTime,120) = '2011-08-14' then [pz] end) as [pz(08-14)] ,max(case when convert(varchar(10),[date_rk],120) = '2011-08-14' then [rk] end) as [rk(08-14)] ,max(case when convert(varchar(10),[date_sg],120) = '2011-08-14' then [sg] end) as [sg(08-14)] ,max(case when convert(varchar(10),[date_xg],120) = '2011-08-14' then [xg] end) as [xg(08-14)] ... ,max(case when convert(varchar(10),[date_xg],120) = '2011-08-17' then [xg] end) as [xg(08-17)] from tab group by itemCode
--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同) select 姓名 as 姓名 , max(case 课程 when '语文' then 分数 else 0 end) 语文, max(case 课程 when '数学' then 分数 else 0 end) 数学, max(case 课程 when '物理' then 分数 else 0 end) 物理 from tb group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同) declare @sql varchar(8000) set @sql = 'select 姓名 ' select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']' from (select distinct 课程 from tb) as a set @sql = @sql + ' from tb group by 姓名' exec(@sql) --SQL SERVER 2005 静态SQL。 select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。 declare @sql varchar(8000) select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程 set @sql = '[' + @sql + ']' exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------
--不是很理解,统计这四个日期字段自己对应的数据
--这样?
select [dateAndTime] from dateAndTime d left join table1 tb
on d.pz = tb.pz
,max(case when convert(varchar(10),dateAndTime,120) = '2011-08-14' then [pz] end) as [pz(08-14)]
,max(case when convert(varchar(10),[date_rk],120) = '2011-08-14' then [rk] end) as [rk(08-14)]
,max(case when convert(varchar(10),[date_sg],120) = '2011-08-14' then [sg] end) as [sg(08-14)]
,max(case when convert(varchar(10),[date_xg],120) = '2011-08-14' then [xg] end) as [xg(08-14)]
...
,max(case when convert(varchar(10),[date_xg],120) = '2011-08-17' then [xg] end) as [xg(08-17)]
from tab
group by itemCode
,max(case when convert(varchar(10),dateAndTime,120) = '2011-08-14' then [pz] end) as [pz(08-14)]
,max(case when convert(varchar(10),[date_rk],120) = '2011-08-14' then [rk] end) as [rk(08-14)]
,max(case when convert(varchar(10),[date_sg],120) = '2011-08-14' then [sg] end) as [sg(08-14)]
,max(case when convert(varchar(10),[date_xg],120) = '2011-08-14' then [xg] end) as [xg(08-14)]
...
,max(case when convert(varchar(10),[date_xg],120) = '2011-08-17' then [xg] end) as [xg(08-17)]
from tab
group by itemCode
,max(case when convert(varchar(10),dateAndTime,120) = '2011-08-14' then [pz] end) as [pz(08-14)]
,max(case when convert(varchar(10),[date_rk],120) = '2011-08-14' then [rk] end) as [rk(08-14)]
,max(case when convert(varchar(10),[date_sg],120) = '2011-08-14' then [sg] end) as [sg(08-14)]
,max(case when convert(varchar(10),[date_xg],120) = '2011-08-14' then [xg] end) as [xg(08-14)]
...
,max(case when convert(varchar(10),[date_xg],120) = '2011-08-17' then [xg] end) as [xg(08-17)]
from tab
group by itemCode
,max(case when convert(varchar(10),dateAndTime,120) = '2011-08-14' then [pz] end) as [pz(08-14)]
,max(case when convert(varchar(10),[date_rk],120) = '2011-08-14' then [rk] end) as [rk(08-14)]
,max(case when convert(varchar(10),[date_sg],120) = '2011-08-14' then [sg] end) as [sg(08-14)]
,max(case when convert(varchar(10),[date_xg],120) = '2011-08-14' then [xg] end) as [xg(08-14)]
...
,max(case when convert(varchar(10),[date_xg],120) = '2011-08-17' then [xg] end) as [xg(08-17)]
from tab
group by itemCode
--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------