這是一個交叉表的問題,处你在sql幫助里查"交叉表"可以查到一些有用的幫助.
在CSDN本版也有很多這種問題,你搜一下吧.
在CSDN本版也有很多這種問題,你搜一下吧.
解决方案 »
- sql server 查询时间段里特定时间间隔的所有时间
- 请教 一个 简单的 select 语句
- 查找不包括在字段值记录里面的语句怎么写
- 赠分贴:请 dobear_0922 进来接分,以特别答谢你的帮助。
- 内查询的问题,请大家帮看看这个结果的SQL如何写
- 100分!!程序找不到服务器,请执行 sp_addlinkedserver 以将服务器添加到 sysservers
- DTS在存储过程里调问题?
- 关于sql7和sql2000的连接问题。
- SQL高手請進.............
- 求助:SQL Server2016Developer Edition安装失败
- 考考你的SQL?我是做不出来。
- 如何设计<考试系统>的表结构
pengdali(大力)好象今天休假?
case when (depart='0002' and event='in') then time end as '0002in' ...
from table2
Declare @Str VarChar(8000)
Set @Str=''
Select @Str=@Str+'D'+a.Depart+IsNull(Event,'N')+'=Max(Case when Event='''+IsNUll(Event,'In')+''' then Time else Null end),' from Dep a,Detail b
where a.Depart*=b.Depart
Select @Str
Set @Str=Left(@Str,Len(@Str)-1)
Exec('Select JobNo,'+@Str+' From Detail Group by JobNo')
http://expert.csdn.net/Expert/topic/2041/2041208.xml?temp=.6613733
set @sql='select JobNo '
select @sql=@sql+char(13)
+',min(case when Depart ='''+Depart
+''' and Event=''in'' then Time end) as ['+Depart
+'in]'+char(13)
+',max(case when Depart ='''+Depart
+''' and Event=''out'' then Time end) as ['+Depart
+'out]'
from Depart
set @sql=@sql+char(13)+'from table1 group by JobNo'
exec(@sql)
set @sql='select JobNo '
select @sql=@sql+char(13)
+',min(case when Depart ='''+Depart
+''' and Event=''in'' then Time end) as ['+Depart
+'in]'+char(13)
+',max(case when Depart ='''+Depart
+''' and Event=''out'' then Time end) as ['+Depart
+'out]'
from Depart
set @sql=@sql+char(13)+'from table1 group by JobNo'
exec(@sql)
insert #a values('0001')
insert #a values('0002')
insert #a values('0003')
create table #b (JobNo varchar(100),Depart varchar(100),Event varchar(100),[Time] datetime)
insert #b values('001','0001','in','2003/07/01')
insert #b values('001','0001','out','2003/07/02')
insert #b values('001','0002','in','2003/07/03')
insert #b values('001','0002','out','2003/07/04')
insert #b values('002','0001','in','2003/07/05')
insert #b values('002','0001','out','2003/07/06')
declare @sql varchar(8000)
set @sql = 'select JobNo'
select @sql = @sql + ',max(case when Depart='''+cast(Depart as varchar(10))+''' and Event='''+Event
+''' then [Time] end) ['+cast(Depart as varchar(10))+Event+']'
from (select a.depart,b.Event from #a a,(select distinct Event from #b) b ) tem order by depart,Event
select @sql = @sql+' from #b group by JobNo'exec(@sql)
go
drop table #a,#b