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:

  1. Perform the concatenation on the client.
  2. 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 :)

Need Your Help

Django (postgresql-psycopg2) connection pool: Simple vs Threaded vs Persistent ConnectionPool

python django connection-pooling psycopg2 django-postgresql

I was looking at db connection pool implementations to use in my Django app using postgresql db backend, and came across this very recent wrapper package implemented for psycopg2 -