troubleshooting Question

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

Avatar of woodwyn
woodwynFlag for United States of America asked on
Microsoft SQL Server
10 Comments1 Solution161 ViewsLast Modified:
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
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
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]+ ', ','')
GROUP BY keyWebItemAttributes
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros