Avatar of woodwyn
woodwyn
Flag for United States of America

asked on 

In SQL 2000 combine mulitple rows into a single row seperated by a semi colon

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
Microsoft SQL Server

Avatar of undefined
Last Comment
woodwyn

8/22/2022 - Mon