sql 2005 grouping data that is dynamic

Question

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

See http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx — Martin Smith, Sep 27 '10 at 22:43
Were you using MySQL, the answer would be GROUP_CONCAT(). Since you aren't, you need an equivalent. See [SO 2852892](http://stackoverflow.com/questions/2852892/is-there-a-group-concat-function-in-ms-access) and [SO 3389347](http://stackoverflow.com/questions/3389347/ssrs-group-concat-equivalent-using-an-expression) — Jonathan Leffler, Sep 27 '10 at 22:46
@Jonathan Leffler: Have to know about GROUP_CONCAT to look for it, but [this question also fits the bill](http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column) — OMG Ponies, Sep 27 '10 at 23:19

score 3 · Accepted Answer · answered Sep 27 '10 at 22:48

3

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

answered Sep 27 '10 at 22:48

LittleBobbyTables - Au Revoir

32,008
25
109
114

Amazing but how does it work?! Any chance you could explain this. I don't want to copy paste...ya know? – EKet Sep 27 '10 at 23:01
1

I think this article covers it better than I could: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/. Basically, XML PATH merges the strings together, and then STUFF trims off the leading comma. – LittleBobbyTables - Au Revoir Sep 27 '10 at 23:07
On second thoughts, looking closer at this my row seems to have duplicated a lot of the contacts. So it says Joe, Billy, James, Joe, Billy James, etc. Any thoughts? – EKet Sep 27 '10 at 23:20
I added select distinct in the inner select...seems to fixed that issue. Still verifying the data. Will post as Answer once I've checked 100%. Thanks for your help! – EKet Sep 27 '10 at 23:29

score 2 · Answer 2 · answered Sep 27 '10 at 22:41

2

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

answered Sep 27 '10 at 22:41

Abe Miessler

82,532
99
305
486

Any more with this? I'd love to see some more about how this fits with grouping. Thanks! – EKet Sep 27 '10 at 23:23

score 2 · Answer 3 · answered Sep 27 '10 at 22:54

2

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

answered Sep 27 '10 at 22:54

Martin Smith

438,706
87
741
845

The subtree cost when I test with a table is atrocious - 2.49187 – OMG Ponies Sep 27 '10 at 23:27
@OMG - But `Bill & Ben` don't become `Bill & Ben`! I haven't tested performance of it. The subtree cost is just a heuristic and doesn't necessarily reflect actual performance - As this shows http://stackoverflow.com/questions/3424650/sql-query-pervious-row-optimisation/3426364#3426364 – Martin Smith Sep 27 '10 at 23:32
Yeah, `FOR XML PATH` does have the issue with special characters. – OMG Ponies Sep 27 '10 at 23:34

sql 2005 grouping data that is dynamic

3 Answers3