We help IT Professionals succeed at work.

How to concatenate one field from similar records

maxwell2323
maxwell2323 asked
on
I have the results of a select query where all records have totally equal field values except for only one field, where the values are different. What I want to do in T-SQL is basically have just one record, where the one particular field has the unique values concatenated together, perhaps separated by commas or slashes or pipes or something (other than a space). For instance, see the file attached that has the results of the query. I want to get this record below out of the results:   Joe      6/1/2005    Spinal injury,valve disease,migraine

resultsofselectquery.doc
Comment
Watch Question

Commented:
This article should lead you to your solution.
 Using COALESCE to Build Comma-Delimited String
http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string 
Kevin CrossChief Technology Officer
Most Valuable Expert 2011
Commented:
That is using dynamic SQL. If you want to do this inline, you can do this using a SQL trick.
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=CreateACommaDelimitedList
The MSDN explains it well, but basics is to group by or get distinct rows of the repeatned portion of data and then for the  unique values you use a subquery with the FOR XML syntax.
Top Expert 2012

Commented:
>>That is using dynamic SQL.<<
You may want to double check that...
Bhavesh ShahLead Analyst
Top Expert 2010
Commented:

Check this out.
This may solve ur query. :-)
SELECT 'JOE' C_NAME,'06/01/2005'DT,'Spinal injury'Cause INTO #TABLE

INSERT INTO [#TABLE]
 VALUES 
( 'JOE','06/01/2005','Valve disease')

INSERT INTO [#TABLE] 
 VALUES 
( 'JOE','06/01/2005','Migraine')


SELECT C_NAME,DT,
	(Stuff((Select ', ' + Cause From #TABLE T2 Where T2.c_NAME = T1.C_NAME and T2.DT = T1.DT FOR XML PATH('')),1,2,'')) as Cause
	 
FROM #TABLE T1
GROUP BY C_NAME,DT

Open in new window

Bhavesh ShahLead Analyst
Top Expert 2010

Commented:

Hey,

I just made code ready made, but idea was same as Mr.Genius - mwvisa1 tolds...

So check dat link..
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Yes Anthony, dynamic SQL is not what I meant. the coalesce approaches depends on filling variable first the using so it works well but will be limited when you can't use multiple statements then can use the xml trick shown...
Kevin

Author

Commented:
Great stuff. Thanks everyone !