Unfortunately still on SQL 2000 here. The following query creates #MSR4 with repeating values of keyWebItemAttributes, each with a unique PersonalizationAnswer.
SELECT p.keyWebItemAttributes, l.PersonalizationAnswer
INTO #MSR4
FROM WebLinkPersonalizations l
INNER JOIN WebItemPersonalizations p ON l.keyWebItemPersonalizations=p.keyWebItemPersonalizations
I want to combine the all the Personalization records for keyWebItemAttributes into a temporary table. I cannot get XML to work here because of the SQL 2000 thing and COELESCE doesn't seem to allow for insertion into temp tables. This gives an error at XML:
SELECT B.keyWebItemAttributes, STUFF((SELECT ', ' + A.Personalization FROM #MSR4 A
Where A.keyWebItemAttributes=B.keyWebItemAttributes FOR XML PATH('')),1,1,'') AS Personalizations
INTO #MSR5
FROM #MSR4 B
Group By B.keyWebItemAttributes
This gives me the error A SELECT INTO statement cannot contain a SELECT statement that assigns values to a variable.
DECLARE @Personalization CHAR(2000)
Set @Personalization=''
Select keyWebItemAttributes, @Personalization=@Personalization + Coalesce([Personalization]+ ', ','')
INTO #MSR5
FROM #MSR4
GROUP BY keyWebItemAttributes