I don't see any way to get the ShiftNames into the SQL except thru dynamic execution. In fact, you'll also need a cursor to build the statement to execute. Maybe something like this (I have NOT tested this code yet):

I know, varchar has low size limit in 6.5 version, but that is all.
I expect I can:
1, select identity into
2, unlimited concatenate in exec
3, use max(case when ...)
4, use derived table
All these things are not needed, only code would be longer.

SELECT OrderID=identity(int,1,1),ShiftID,ShiftName into #Shifts
FROM WebSatRotaDetails
WHERE RotaId= 6
ORDER BY ShiftName
declare @Name1 varchar(200),@Name2 varchar(200),@Name3 varchar(200),@Name4 varchar(200),@Name5 varchar(200)
,@Name6 varchar(200),@Name7 varchar(200),@Name8 varchar(200),@Name9 varchar(200),@Name10 varchar(200)
select @Name1='',@Name2='',@Name3='',@Name4='',@Name5='',@Name6='',@Name7='',@Name8='',@Name9='',@Name10=''
select @Name1 =' "'+ShiftName+'"=max(case when ShiftID='+ShiftID+' then Name end)' from #Shifts where OrderID=1
select @Name2 =',"'+ShiftName+'"=max(case when ShiftID='+ShiftID+' then Name end)' from #Shifts where OrderID=2
select @Name3 =',"'+ShiftName+'"=max(case when ShiftID='+ShiftID+' then Name end)' from #Shifts where OrderID=3
select @Name4 =',"'+ShiftName+'"=max(case when ShiftID='+ShiftID+' then Name end)' from #Shifts where OrderID=4
select @Name5 =',"'+ShiftName+'"=max(case when ShiftID='+ShiftID+' then Name end)' from #Shifts where OrderID=5
select @Name6 =',"'+ShiftName+'"=max(case when ShiftID='+ShiftID+' then Name end)' from #Shifts where OrderID=6
select @Name7 =',"'+ShiftName+'"=max(case when ShiftID='+ShiftID+' then Name end)' from #Shifts where OrderID=7
select @Name8 =',"'+ShiftName+'"=max(case when ShiftID='+ShiftID+' then Name end)' from #Shifts where OrderID=8
select @Name9 =',"'+ShiftName+'"=max(case when ShiftID='+ShiftID+' then Name end)' from #Shifts where OrderID=9
select @Name10=',"'+ShiftName+'"=max(case when ShiftID='+ShiftID+' then Name end)' from #Shifts where OrderID=10
exec('select "Day",'+@Name1+@Name2+@Name3+@Name4+@Name5+@Name6+@Name7+@Name8+@Name9+@Name10
+' from (
SELECT "Day"=convert(varchar(20),S.Day,103)
,S.ShiftId
,"Name"=A.Forename +'' ''+ A.Surname
FROM Applicants A
,WebSatRotaschedule S
,WebSatRotaDetails D
,WebSatRota R
WHERE S.Day BETWEEN CONVERT(DATETIME, ''02/21/2003'') and CONVERT(DATETIME, ''02/28/2003'')
AND S.RotaId = D.RotaId
AND S.ShiftId = D.ShiftId
AND D.RotaId = R.RotaId
AND R.RotaId = 6
AND A.ApplicantID = S.DoctorID
) x GROUP BY "Day" ORDER BY "Day"')

0

Featured Post

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat. The purpose of this eBook is to educate the reader about ransomware attacks.

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed.
Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.

Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.