if exists(select name from tempdb.dbo.sysobjects where id = object_id(N'tempdb.dbo.#tempWorkDate') and type='u')
drop table #tempWorkDate CREATE TABLE [#tempWorkDate] ( [WorkDate] [varchar] (100) NULL,[SeqNo] [int] NULL )
Insert Into #tempWorkDate ([WorkDate],[SeqNo] ) values ( '2011-07-04','1' )
Insert Into #tempWorkDate ([WorkDate],[SeqNo] ) values ( '2011-07-05','2' )
Insert Into #tempWorkDate ([WorkDate],[SeqNo] ) values ( '2011-07-06','3' )
Insert Into #tempWorkDate ([WorkDate],[SeqNo] ) values ( '2011-07-07','4' )
Insert Into #tempWorkDate ([WorkDate],[SeqNo] ) values ( '2011-07-08','5' )
Insert Into #tempWorkDate ([WorkDate],[SeqNo] ) values ( '2011-07-09','6' )
Insert Into #tempWorkDate ([WorkDate],[SeqNo] ) values ( '2011-07-10','7' )
Insert Into #tempWorkDate ([WorkDate],[SeqNo] ) values ( '2011-07-11','8' )
Insert Into #tempWorkDate ([WorkDate],[SeqNo] ) values ( '2011-07-12','9' )
declare @test varchar(8000)
set @test=''
select @test=@test+','+char(10)+'(case when workdate='+QUOTENAME(WorkDate,N'''')+' then TravelTypeID else null end )A'+cast(SeqNo as varchar)
from #tempWorkDate
order by WorkDate
print @test