CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
EKet
EKet

Reputation: 7314

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 like 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

Upvotes: 2

Views: 102

Answers (3)

Martin Smith
Martin Smith

Reputation: 453687

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

Upvotes: 2

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32720

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

Upvotes: 3

Abe Miessler
Abe Miessler

Reputation: 85116

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

Upvotes: 2

Related Questions