How to concatenate data output separated values?

Question

I'm creating a view to serve as a matrix report that displays row data from various tables as columns. I think it can achieve this through concatenation but I’m a little stuck. My query works excellent however I’m using an inversed datatable (pivot table) which is displayed in a gridview and what I’m getting at present is all my multiple values are adding together in each cell!

Set Name    James   Smith   Jones
Training 1      9       5       7
Training 2      5       7       8
Training 3     12      10       9

This is what I'd like to output!

Set Name    James    Smith     Jones
Training 1  3,1,2,3  2,3       5,2
Training 2  4,1      1,1,5     2,2,2,2 
Training 3  1,4,4,3  2,2,2,4   1,3,2,3

I have a many-to-many relationship between users and training since one user can have many instances of training with multiple outcomes over certain periods of time.

The userProfilesTbl This table stores additional user information from the ASP.NET membership, roles and profiles tables.

•   PK UserId (int) 
•   StaffNo (varchar) 
•   forename (varchar) 
•   surname (varchar) 
•   FK st_id (int)

The storeTbl This table references all of the stores in the group

•   PK st_id (int) 
•   store (varchar) 
•   storeNo (varchar) 
•   FK d_id (int)

The shiftTbl this table references the differing shift patterns

•   PK wa_id (int) 
•   Shift (varchar)

The userAssessmentTbl this table holds information relating to the user, the training which was undertaken and the outcome of that training. The o_id can only be one value 1, 2, 3 or 4

•   PK ti_id (int) 
•   FK UserId (UID) 
•   FK tt_id (int) 
•   o_id (int) 
•   n_id (int)

The setTbl this table is the names of each of the training courses

•   PK s_id (int) 
•   setName (varchar) 
•   instruction (varchar) 
•   link (varchar) 
•   setNo (int)

This is my sql query string and if I do plain LEFT JOIN's on the tables using the SQL below, I will get several rows related to one person:

Dim query As String = "SELECT * 
                       FROM userProfilesTbl 
                       LEFT JOIN storeTbl ON userProfilesTbl.st_id = storeTbl.st_id 
                       LEFT JOIN shiftTbl ON userProfilesTbl.wa_id = shiftTbl.wa_id
                       LEFT JOIN userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId 
                       LEFT JOIN trainingTbl ON userAssessmentTbl.tt_id = trainingTbl.tt_id
                       LEFT JOIN setsTbl ON trainingTbl.s_id = setsTbl.s_id 
                       WHERE userProfilesTbl.st_id=@st_id 
                       AND userProfilesTbl.wa_id=@wa_id 
                       AND DATEPART(mm,t_date) = @m_date 
                       AND DATEPART(yyyy,t_date) = @y_date  
                       ORDER BY surname ASC"

Any ideas?

Possible duplicate of [Merge row values into a CSV (a.k.a GROUP\_CONCAT for SQL Server)](http://stackoverflow.com/questions/7755815/merge-row-values-into-a-csv-a-k-a-group-concat-for-sql-server). This will allow you to concatenate the values per some value — gbn, Jan 16 '13 at 12:10
Using SQL Server? This question looks like it has the answer: http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server — , Jan 16 '13 at 12:13

How to concatenate data output separated values?

0 Answers0