Thanks for you reply.

Ok. lets say that I've got dynamic pivot what do I do next?

Do I have to create dynamic sql to include all CriticalCareAcivityCodes?

Have a look at new output from #picu table.

Any chance of getting bit of code back?

Thanks

Mike

create table #picu

(

EpisodeId int,

CriticalCareLocalIdentifier varchar(10),

CriticalCareStartDate varchar(10),

ActivityDate varchar(10),

CriticalCareActivityCode1 varchar(2),

CriticalCareActivityCode2 varchar(2),

CriticalCareActivityCode3 varchar(2),

CriticalCareActivityCode4 varchar(2),

CriticalCareActivityCode5 varchar(2),

CriticalCareActivityCode6 varchar(2)

)

insert into #picu values (6497895,'185719','2012-05-27','2012-05-28','11','22','33','44','55','66')

insert into #picu values (6582674,'215364','2012-10-30','2012-11-01','77','88','99','11',null,null)

insert into #picu values (6582674,'215364','2012-10-30','2012-11-02','99','11',null,null,null,null)

select * from #picu