11.txt

sql中检索行列转换,将多行的数据转成一行,然后增加列数

select 姓名,m.工号,年份=year(日期),月份=month(日期),序号=day(日期),日期,
  max(case px when 1 then 时间 else '' end) C1,
  max(case px when 2 then 时间 else '' end) C2,
  max(case px when 3 then 时间 else '' end) C3,
  max(case px when 4 then 时间 else '' end) C4,
  max(case px when 5 then 时间 else '' end) C5,
  max(case px when 6 then 时间 else '' end) C6
from
( 
  select * , px = (select count(*) from 当前记录表  where 工号 = t.工号 and 日期 = t.日期 and 时间 < t.时间) + 1 from 当前记录表 t  
) m ,人事表 a where m.工号=a.工号 and m.工号='00000523' and 日期>='2009-03-01'
group by 姓名,m.工号,year(日期),month(日期),day(日期),日期


lotus script脚本

Sub Click(Source As Button)
	Dim s As New NotesSession
	Dim w As New NotesUIWorkspace
	Dim db As NotesDatabase
	Dim uidoc As NotesUIDocument
	Dim doc As NotesDocument
	Dim hint11 As String
	Dim subject11 As String
	Dim tmp As String 
	Dim currentstatus As String
	Dim m As String 
	
	Set db=s.CurrentDatabase
	Set uidoc = w.CurrentDocument
	Set doc = uidoc.Document
	hint11="电脑5S稽核报告点击可连接到相关表单"	
	subject11="电脑5S稽核"
	If doc.IsNewNote Then
		If(doc.jhxs(0)="") Then
			Messagebox("稽核形式不能为空")
			Exit Sub
		End If
		If(doc.jhxs(0)="1") Then 
			doc.jhfs1="全查"
			doc.jhfs2="全查"
			Call send(doc,"rihui wang/tongying",hint11,subject11)
		End If
		If(doc.jhxs(0)="2") Then 
			doc.jhfs1="抽查"
			doc.jhfs2="抽查"
			Call send(doc,"rihui wang/tongying",hint11,subject11)
		End If
		If(doc.jhxs(0)="3") Then 
			doc.jhfs1="复查"
			doc.jhfs2="复查"
		End If
		Call doc.ReplaceItemValue ("id",0)	
		Call doc.Save(False,True)
		Call uidoc.Save
		uidoc.EditMode=False
		Call uidoc.Close
	Elseif doc.id(0)=1 Then
		m=uidoc.FieldGetText ("jhfs2")
		currentstatus= uidoc.FieldGetText ("jhxs")
		If (currentstatus = m) Then
			Call doc.Save(False,True)
			Call uidoc.Save
			uidoc.EditMode=False
			Call uidoc.Close
		Else
			Messagebox "稽核形式不能更改",0+16,"错误"
			Call doc.ReplaceItemValue ("jhxs",m)
			Call uidoc.Refresh 
			Exit Sub
		End If 	
		
	Else
		m=uidoc.FieldGetText ("jhfs2")
		currentstatus= uidoc.FieldGetText ("jhxs")
		If (currentstatus <> m) Then
			Messagebox "稽核形式不能更改11",0+16,"错误"
			Call doc.ReplaceItemValue ("jhxs",m)
			Call uidoc.Refresh 
			Exit Sub
		Else
			Call doc.Save(False,True)
			Call uidoc.Save
			uidoc.EditMode=False
			Call uidoc.Close
		End If 	
		
	End If
	
End Sub