How to display multiple row in a single row which as same description_id in another table in sql server 2008?
description_id description -------------- ------------- 1 Ampicillin Oral 1 Ofloxacin Oral 1 Sulfamoxole+Trimethoprim Oral 2 Letrozole Oral 2 Flunarizine Oral
description_id from table1 and description from table2.
and result should be
description_id description -------------- ----------- 1 Ampicillin Oral,Ofloxacin Oral, Sulfamoxole+Trimethoprim Oral 2 Letrozole Oral, Flunarizine Oral
I'm assuming table2 also has a description_id column, and that it is the primary key in dbo.table1:
SELECT description_id, STUFF((SELECT ',' + description FROM dbo.table2 WHERE description_id = t1.description_id FOR XML PATH('')), 1, 1, '') FROM dbo.table1 AS t1;
I think what you're looking for is a an aggregate function that can concatenate the description strings together.
However, although SQL Server has built in functions for aggregating numbers (e.g. SUM and AVERAGE), there is no built-in aggregate string concatenation function.
This leaves you with a two main options:
- Perform the concatenation on the client.
- Use a workaround technique such as XML PATH, a recursive CTE, or using an embedded Common Language Runtime package.
For option 2, there are some articles on how to do this around, e.g. this article about concatenating row values. And indeed - I've just seen Aaron Bertrand's answer come in, which shows you exactly how to do it in your situation :)