Concatenating rows into single column when taking value from two different tables

Question

Possible Duplicate:
Concatenate values based on ID

I have two tables

table1 contains

pkUserSubjectid  UserId  fkSubjectId
   15              146     1
   16              146     2
   17              146     4
   18              147     1
   19              147     3
   20              148     1
   21              148     3
   22              149     1
   23              149     3

table 2 contains

pkSubjectId   SubjectName
      1        Maths
      2        English
      3        Physics
      4        Chemistry
      5        Computer

I want my result in this format

 UserId     SubjectName
   146       Maths, English, Chemistry
   147       Maths, Physics

and so on

Please tell me any query in SQL

Depending on the DBMS you use there are different solutions. What do you use? SQL Server, Oracle, MySQL, SQLite, PostgreSQL or perhaps something else? — Mikael Eriksson, Apr 07 '12 at 07:05

score 0 · Accepted Answer · answered Apr 07 '12 at 09:45

Consider building a clr aggregate function. The msdn example function would work for this.

http://msdn.microsoft.com/en-us/library/ms131056(v=sql.100).aspx

You could then do something like

SELECT a.[UserId], dbo.MyAgg(b.[SubjectName]) as [SubjectName] 
FROM table1 as a 
LEFT OUTER JOIN table2 as b ON a.[fkSubjectId] = b.[pkSubjectId] 
GROUP BY a.[UserId]

The example uses a single parameter and uses "," as the delimiter. You could also create a 2 parameter function as in the second example to pass in the delimiter.

Concatenating rows into single column when taking value from two different tables

1 Answers1