Have you ever wanted to concatenate a repeating column as a comma delimitated string in the same query result? Normally, I would use a Scalar Valued Function to COALESCE the values and return them to the calling query (as in the recent post here by Dustin Brisson), but there is a way to do this all in a single request.
SELECT DISTINCT
UserID,
STUFF(
(SELECT ',' + SubTableUser.UserRole
FROM TableUser AS SubTableUser
WHERE SubTableUser.UserID = TableUser.UserID
FOR XML PATH('')), 1, 1, '') AS UserRole
FROM TableUser
ORDER BY UserID
Above is a query with a sub-query. The parent query using DISTINCT against the record values that will remain the same during the repeated result set. The result set in this case happens because there are several users in this fictional database that have more than one user role assigned.
The sub-query, concatenates a comma in front of each additional result for the user role obtained when the user ID of the sub-query matches the distinct user ID of the parent query. This results in a very nice string with an awful leading comma.
You could strip out the comma whatever will display the results, but here we are using the STUFF command to take the first character, in this case a comma, and replace it with nothing. This results in a nice clean list of user roles for each distinct user ID.
If you find this post useful please leave a comment and let me know how you used the information.
Basically, I have the 3 tables with sample data as below and I want to concatenate the last name after one another with a comma for each ID that has more than 1 EMPLID that share the same R_TYP_C.
This is what I need for the result with the sample data shown from the tables below:
CPO_I;R_TYP_C,R_TYP_X,EMPLID;L_NAME
001;1;TECH_OWNER;1009,2001;STRANGE,STEVENS
002;2;BA; 4001;NEWTONS
Table1:
CPO_I; EMPLID; R_TYP_C
001; 1009; 1
001; 2001; 1
001; 3001; 4
002; 4001; 2
Table 2:
EMPLID; L_NAME; EMAIL
1009; Strange; strange.fname@yahoo.com
2001; stevens; stevens.fname@yahoo.com
3001; cyclop; cyclop.fname@gmail.com
4001; newtons; newtons.fname@gmail.com
Table3:
R_TYP_C; R_TYP_X
1; Tech_Owner
2; BA
3; Sec Cord
4; Ret Author