现有表
TrainerName Session ClassName Date
Benjamin AM CRS TRAINING 2012-03-08
Benjamin AM CRS TRAINING 2012-03-09
Benjamin AM Microsoft Exchange Server 2010 2012-03-08
Benjamin AM Microsoft Exchange Server 2010 2012-03-09
需要转换成
TrainerName Session ClassName '2012-03-08' '2012-03-09'
Benjamin AM CRS TRAINING CRS TRAINING CRS TRAINING
Benjamin AM Microsoft Exchange Server 2010 Microsoft Exchange Server 2010 Microsoft Exchange Server 2010在线等高手帮忙!谢谢!
TrainerName Session ClassName Date
Benjamin AM CRS TRAINING 2012-03-08
Benjamin AM CRS TRAINING 2012-03-09
Benjamin AM Microsoft Exchange Server 2010 2012-03-08
Benjamin AM Microsoft Exchange Server 2010 2012-03-09
需要转换成
TrainerName Session ClassName '2012-03-08' '2012-03-09'
Benjamin AM CRS TRAINING CRS TRAINING CRS TRAINING
Benjamin AM Microsoft Exchange Server 2010 Microsoft Exchange Server 2010 Microsoft Exchange Server 2010在线等高手帮忙!谢谢!
create table car
(TrainerName varchar(10), Sessionx varchar(5), ClassName varchar(30), Dates date)insert into car
select 'Benjamin', 'AM', 'CRS TRAINING', '2012-03-08' union all
select 'Benjamin', 'AM', 'CRS TRAINING', '2012-03-09' union all
select 'Benjamin', 'AM', 'Microsoft Exchange Server 2010', '2012-03-08' union all
select 'Benjamin', 'AM', 'Microsoft Exchange Server 2010', '2012-03-09'
select TrainerName, Sessionx, ClassName0, [2012-03-08], [2012-03-09]
from (select TrainerName, Sessionx, ClassName ClassName0, ClassName ClassName1, Dates from car) t
pivot(max(ClassName1) for Dates in ([2012-03-08],[2012-03-09])) tTrainerName Sessionx ClassName0 2012-03-08 2012-03-09
----------- -------- ------------------------------ ------------------------------ ------------------------------
Benjamin AM CRS TRAINING CRS TRAINING CRS TRAINING
Benjamin AM Microsoft Exchange Server 2010 Microsoft Exchange Server 2010 Microsoft Exchange Server 2010(2 row(s) affected)