Display value row into column in SQL Server

Question

I have a table with these sample records:

CREATE TABLE USERROLES(User ID int,ApplicationID int,Roleid INT) 

INSERT INTO USERROLES
VALUES (11, 1, 1), (11, 1, 5), (11, 3, 5);

I want to display below output.

USERID  ApplicationID   Roles
-----------------------------
11         1             1,5
11         3              5

ApplicationID=1 is duplicates for ROLES 1,5.So it should display in single row. I am using SQL Server 2016

You may read this: https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005. — Zhorov, Oct 27 '18 at 06:43

score 2 · Accepted Answer · answered Oct 27 '18 at 06:43

You can try to use STUFF with FOR XML let Roleid combine.

 SELECT 
  UserID,
  ApplicationID,
  STUFF((
    SELECT ',' + cast(Roleid as varchar(max))
    FROM USERROLES tt
    WHERE ApplicationID = t1.ApplicationID
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,1,'') AS Roles
FROM USERROLES t1
group by 
  UserID,
  ApplicationID

sqlfiddle

Result

serID   ApplicationID   Roles
11       1              1,5
11       3              5

score 2 · Answer 2 · answered Oct 27 '18 at 07:13

You can try the below query

    SELECT DISTINCT UserID, ApplicationID, Roles = 
    STUFF((SELECT ', ' + CAST(Roleid as Varchar)
           FROM userroles b 
           WHERE b.UserID = a.UserID and a.ApplicationID = b.ApplicationID 
          FOR XML PATH('')), 1, 2, '')
FROM userroles a
GROUP BY UserID, ApplicationID, Roleid

Hope this will help you.

score 1 · Answer 3 · answered Oct 31 '18 at 10:53

Sometime adding other string with comma add extra space between both, so use
FOR XML PATH('')), 1, 1, '')

instead of

 FOR XML PATH('')), 1, 2, '')

as shown below and remove space after comma like ','

SELECT DISTINCT UserID, ApplicationID, Roles = 
    STUFF((SELECT ',' + CAST(Roleid as Varchar)
           FROM userroles b 
           WHERE b.UserID = a.UserID and a.ApplicationID = b.ApplicationID 
          FOR XML PATH('')), 1, 1, '')
FROM userroles a
GROUP BY UserID, ApplicationID, Roleid

Display value row into column in SQL Server

3 Answers3