Comma seperated value on three tables in sql server

Question

I have three tables such as

User

+--+------+
|Id|Name  |
+--+------+
|1 |Ram   |
+--+------+
|2 |Rama  |
+--+------+
|3 |Leesa |
+--+------+
|4 |Kelvin|
+--+------+

Role

+--+-------+
|Id|Name   |
+--+-------+
|1 |Admin  |
+--+-------+
|2 |FA     |
+--+-------+
|3 |Testing|
+--+-------+
|4 |IT     |
+--+-------+

User Role

+--+-------+-------+
|Id|User Id|Role Id|
+--+-------+-------+
|1 |1      |1      |
+--+-------+-------+
|2 |1      |2      |
+--+-------+-------+
|3 |2      |3      |
+--+-------+-------+
|4 |2      |1      |
+--+-------+-------+
|5 |3      |2      |
+--+-------+-------+
|6 |3      |3      |
+--+-------+-------+
|7 |4      |4      |
+--+-------+-------+
|8 |4      |2      |
+--+-------+-------+

From these 3 tables I want output like that

+--+---------+--------+
|Id|User Name|Roles   |
+--+---------+--------+
|1 |Ram      |Admin,FA|
+--+---------+--------+

with cte as ( select a.id, c.name as user1, b.name as role1 from userrole a join role1 b on a.roleid= b.id join user1 c on c.id = a.userid ) select user1, stuff((select ', ' + role1 from cte UL1 where UL1.user1 = UL.user1 ORDER BY user1 FOR XML PATH('')),1,2,'') as [role1] from cte UL GROUP BY user1 Sorry answer Post box Not being show on my side So ... — code save, Jan 15 '14 at 10:09

score 0 · Answer 1 · answered Jan 15 '14 at 07:51

You can do by creating a stored procedure.

CREATE PROC UserRoles
(
    @UserId int
)

AS 
begin
DECLARE @roles NVARCHAR(MAX) = ''
SELECT @roles = @roles + ',' + t.Roles
  FROM (SELECT r.NAME AS Roles FROM [User Role] ur 
    Inner JOIN [ROLE] r ON r.Id = ur.RoleId 
    INNER JOIN User1 u ON u.Id = ur.UserID WHERE u.Id = @UserId) t
  SET @roles = RIGHT(@roles, LEN(@roles)-1)
SELECT u.Id AS ID, u.NAME AS UserName, @roles AS Roles FROM USER1 u WHERE u.Id = @UserId

end

score 0 · Answer 2 · answered Jan 15 '14 at 08:08

I would like to propose some function CONCATROWS for a future SQL standard.

SELECT page_id,CONCATROWS('\n',partext) AS pagetext FROM paragraph ORDER BY parposition GROUP BY page_id

I needed this function more-than-I-can-count times... not that I needed it badly, but it would have made life so much easier.

Comma seperated value on three tables in sql server

2 Answers2