This is very cool! I've used PIVOT and CTE's to do string aggregation in the past. but this method is much nicer!

A couple of issues:
- This removes any spaces from [PROJECT NAMES]. If you have projects named "Project 1" and "Project 2", it will return "Project1 ; Project2" - Your query, as written, will still give one row per project per [EMPLOYEE NAME]. Consider removing [PROJECT DURATION] from the GROUP BY clause

Consider something like this:

SELECT
    EMPLOYEE_NAME, 
    LEFT(PROJECT_NAME, LEN(PROJECT_NAME) - 1) as PROJECT_NAME
FROM
    (
    	SELECT 
    		EMPLOYEE_NAME,
    		PROJECT_NAME=
    			REPLACE(
    				REPLACE( 
    					(SELECT PROJECT_NAME FROM EMPLOYEE_PROJECT_DETAILS WHERE EMPLOYEE_NAME=a.EMPLOYEE_NAME FOR XML PATH('')), 
    					'<PROJECT_NAME>', '['), 
    				'</PROJECT_NAME>', '],')			
    	FROM 
    		EMPLOYEE_PROJECT_DETAILS a 
    	WHERE 
    		EMPLOYEE_NAME IS NOT NULL 
    	GROUP BY 
    		EMPLOYEE_NAME
    ) result

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
    WHERE b.EMPLOYEE_NAME=a.EMPLOYEE_NAME
    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
  [Project 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.

Kip, can you re-post this, but select your code and click the button with the 101 label? It looks like your code go munged.

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
    WHERE b.EMPLOYEE_NAME=a.EMPLOYEE_NAME
    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
  [Project Names]
FROM EMPLOYEE_PROJECT_DETAILS a 
WHERE EMPLOYEE_NAME IS NOT NULL 
GROUP BY EMPLOYEE_NAME,PROJECT_DURATION

Thank You Guys for Improvising the query. :)

Thanks, Manjunath C Bhat