sql 2005 grouping data that is dynamic

I have data that looks like this

Investor    Contact
IBM           James  
IBM           Dean  
IBM           Sean  
Microsoft     Bill  
Microsoft     Steve

I need the data to look l开发者_开发知识库ike this

Investor     Contact
IBM          James,Dean,Sean  
Microsoft    Bill,Steve  

OR if the above is impossible

Investor        Contact1  Contact2   Contact3  ...
IBM             James      Dean        Sean  
Microsoft        Bill      Steve

This should work:

SELECT Investor, 
STUFF((
    SELECT ',' + convert(nvarchar(50), Contact) 
    FROM Investors I2
    WHERE I2.Investor = I1.Investor
    FOR XML PATH('')
), 1, 1, '') Contacts
FROM Investors I1
GROUP BY Investor

And result in:

IBM       James,Dean,Sean
Microsoft   Bill,Steve

Try the method below to get your comma separated list going. I'm going to have to play with it some more to figure out how to get the grouping working.

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' , '') + Contact
FROM InvestorContact
SELECT @listStr

Just in case any of your contacts have special XML characters in their names: the Tony Rogerson approach.

;with data as
(
SELECT 'IBM' Investor,  'James' Contact UNION ALL  
SELECT 'IBM' ,          'Dean'  Contact UNION ALL  
SELECT 'IBM' ,          'Sean'  Contact UNION ALL  
SELECT 'Microsoft' ,    'Bill'  Contact UNION ALL    
SELECT 'Microsoft',     'Steve' Contact
)
SELECT Investor, 
   stuff((SELECT mydata
   FROM (
      SELECT ',' + Contact  AS [data()]
      FROM
      data AS d2
      WHERE d2.Investor = d1.Investor
      FOR XML PATH(''), TYPE
   ) AS d ( mydata ) FOR XML RAW, TYPE ).value( '/row[1]/mydata[1]', 'varchar(max)' )
, 1, 1, '')
FROM data d1
GROUP BY Investor