Thanks Manjunath and also Marc. Nice indeed.
Here's one without dropping spaces AND which does not mangle ampersand and other funny characters:
SELECT EMPLOYEE_NAME,PROJECT_DURATION, REPLACE( REPLACE(
(SELECT REPLACE(PROJECT_NAME, ' ', CHAR(127)) AS [data()]
--temporarily replace spaces in names with unprintable CHAR
FROM EMPLOYEE_PROJECT_DETAILS b
FOR XML PATH(''), TYPE).value('.', 'varchar(8000)')
--Looks like Al!Smith Dick!Nixon A&P, with space separating (showing CHAR(127) as !)
,' ',' ; ') --change space separator into ;
,CHAR(127),' ') --restore spaces in names
FROM EMPLOYEE_PROJECT_DETAILS a
WHERE EMPLOYEE_NAME IS NOT NULL
GROUP BY EMPLOYEE_NAME,PROJECT_DURATION
It seems Manjunath had a special character between 'quotes' but it was lost when posting, hence the mysterious replace of empty string with space. So the CHAR(127) above is his idea really.
The .value trick is thanks to KoenV on stackoverflow. Use nvarchar(max) if you need all chars or longer than 8000.
commented on Aug 29 2011 1:25PM