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
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
ASKER CERTIFIED SOLUTION
ste5an
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