触发器与行转列

    Create Trigger OverTime
       On T056F120    --加班記錄修改                 
       for Update                      
     As                                      
       if Update(modify_time)           
       begin       
         Update T089F120
           Set field63=case when i.field9=1 then '一般加班' when i.field9=2 then '兩倍加班' when i.field9=3 then
           '三倍加班' else '' end +'<br>'+convert(varchar(20),i.field17,120) +'<br>'+convert(varchar(20),i.field18,120)
           From T089F120 a , Deleted   d ,Inserted i   
           Where a.field51= convert(varchar(20), d.field17,111) and d.field46=a.field43
       end   
     
      
      
       CREATE Trigger Overtime_insert
       On T056F120    --加班記錄插入                
       for insert                      
     As                                                
       begin 
       declare @type int
       declare @begintime datetime
       declare @endtime datetime
       declare @emp_id varchar(20)
       select @type=field9 ,@begintime=field17,@endtime=field18,@emp_id=field46 from Inserted     
         Update T089F120
           Set field63=case when @type=1 then '一般加班' when @type=2 then '兩倍加班' when @type=3 then
           '三倍加班' else '' end +'<br>'+convert(varchar(20),@begintime,120) +'<br>'+convert(varchar(20),@endtime,120)
           where field51=convert(varchar(20),@begintime,111)  and field43=@emp_id   
         print(@emp_id+convert(varchar(20),@begintime,111)+'     '+cast( @endtime as varchar(100)))
       end   

 

 

 

 

      
      
      
    Create Trigger Jholiday
       On T055F120    --請假記錄修改                 
       for Update                      
     As                                      
       if Update(modify_time) 
         begin
        declare @count  int
        declare @i int
        select @count=max(id) from Inserted
        print(@count)      
       set @i=1
       while @i<=@count   
       begin 
         if exists(select * from Inserted where id=@i)
          begin
         declare @temp varchar(20)
         declare @datebegin datetime
         declare @dateend datetime
         declare @emp_id varchar(20)
              select @temp=field6 from T064F010 where field1 = (select field41 from Inserted where id=@i)
              select @emp_id=field51,@datebegin=field17,@dateend=field18 from Inserted where id=@i           
         Update T089F120
           Set field60=@temp+'<br>'+convert(varchar(20),@datebegin,120) +'<br>'+convert(varchar(20),@dateend,120) 
           Where field51 between convert(varchar(10), @datebegin,111) and convert(varchar(10), @dateend,111)  and @emp_id=field43
         end
        set @i=@i+1
       end   
      

 

CREATE Trigger Jholiday_insert
       On T055F120    --加班記錄插入               
       for insert                      
     As                                                
       begin 
       declare @type varchar(20)
       declare @temp varchar(20)
       declare @begintime datetime
       declare @endtime datetime
       declare @emp_id varchar(20)
       select @type=field41 ,@begintime=field17,@endtime=field18,@emp_id=field51 from Inserted
       select @temp=field6 from T064F010 where field1 =@type     
         Update T089F120
           Set field60= @temp+'<br>'+convert(varchar(20),@begintime,120) +'<br>'+convert(varchar(20),@endtime,120)
           where field51 between convert(varchar(10),@begintime,111) and convert(varchar(10),@endtime,111)  and field43=@emp_id   
         print(@emp_id+convert(varchar(20),@begintime,111)+'     '+cast( @endtime as varchar(100)))
       end   

 

 

//實例
CREATE Trigger mytri
       On division                     
       for Update                      
     As                                      
       if Update(name)           
       begin
         declare @name varchar(20)
         declare @id varchar(10)
        declare @count  int
        declare @i int
        declare @sql varchar(50)
       --  select @name=name from Inserted
         select @count=max(id) from Inserted
        print(@count)
       set @name=''
       set @i=1
       while @i<=@count
       begin  
  print(@i)
            if exists (select * from Inserted where id=@i)
            begin
            select @name=name from Inserted where id=@i
          Update address_book
           Set name1=@name
           From address_book a
           Where a.division_id=@i
         print(@name)
        end
     set @i=@i+1
      end
        --  raiserror('??！?卷??存小于等于0，不能?售',16,1)
        --  rollback
        --  return
       end   

 

 

 

 

create table tb(studentId varchar(10) ,studentName varchar(10) , course varchar(10) , studentScore int)
insert into tb values('1083' ,'name1' , 'chinese' , 75)
insert into tb values('1083' ,'name1' , 'math' , 84)
insert into tb values('1083' ,'name1' , 'English' , 92)
insert into tb values('1084' ,'name2' , 'chinese' , 84)
insert into tb values('1084' ,'name2' , 'math' , 93)
insert into tb values('1084' ,'name2' , 'English' , 87)
go

--SQL SERVER 2000
select studentId,studentName,
  max(case course when 'chinese' then studentScore else 0 end) chinese,
  max(case course when 'math' then studentScore else 0 end) math,
  max(case course when 'English' then studentScore else 0 end) English
from tb
group by studentId,studentName

declare @sql varchar(8000)
set @sql = 'select studentId,studentName'
select @sql = @sql + ' , max(case course when ''' + course + ''' then studentScore else 0 end) [' + course + ']'
from (select distinct course from tb) as a
set @sql = @sql + ' from tb group by studentId,studentName'
exec(@sql)