Combine the Rows into single row in SQL Server

Question

DECLARE @Count BIGINT 

SELECT @Count = Count(ID) 
FROM Users; 

SELECT TOP 50  
    CustUser.[ID],
    CustUser.[FirstName] + ' ' + CustUser.[LastName] FirstName,
    CustUser.[NickName],
    CustUser.[UserName],
    R.[Name] Roles     
FROM
    (SELECT       
         ROW_NUMBER() OVER(ORDER BY US.ID Desc) AS Row,
         US.[ID], US.[FirstName], US.[LastName], US.[NickName],
         US.[UserName], US.[Password]
     FROM 
         [Users] US) CustUser
LEFT JOIN 
    Category CL ON CustUser.[LoginModeCode] = CL.CategoryCode
LEFT JOIN 
    Category CS ON CustUser.[StatusCode] = CS.CategoryCode 
LEFT JOIN
    UserRoles UR ON UR.UserID = CustUser.ID
LEFT JOIN
    Roles R ON R.ID = UR.RoleID
WHERE 
    CustUser.ID = 3 AND 
    [Row] > (1 - 1) * 50  
ORDER BY
    FirstName

This query returns the below output

ID   FirstName       NickName          UserName             Roles                
----------------------------------------------------------------------------
3     ram              jk                 ram               Developer          
3     ram              jk                 ram               TeamLeader

Roles only different in above rows. I am combining two rows.

But I want this output

 ID   FirstName   NickName    UserName    Roles                                            
 --------------------------------------------------------------
 3    ram            jk         ram       Developer, TeamLeader

Check the use of CALESCE (see: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-2017). — FDavidov, Apr 26 '18 at 08:40

score 0 · Accepted Answer · answered Apr 26 '18 at 08:28

--Test Data
CREATE TABLE #Temp_table(
    ID   int,
    FirstName            nvarchar(200),
    NickName   nvarchar(200),
    UserName             nvarchar(200),
    Roles                 nvarchar(200)
);
INSERT INTO #Temp_table VALUES (3,'ram','jk','ram','Developer');
INSERT INTO #Temp_table VALUES (3,'ram','jk','ram','TeamLeader');
INSERT INTO #Temp_table VALUES (3,'ram','jk','ram','XXXLeader');

--change temp_table to your select table
select ID,FirstName,NickName,UserName,
    STUFF((
        SELECT ', ' + Roles 
        FROM #Temp_table 
        FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
    ,1,2,'') Roles
from #Temp_table
group by ID,FirstName,NickName,UserName;

PS. change temp_table to your Subqueries – Wei Lin Apr 26 '18 at 08:52 — Wei Lin, Apr 26 '18 at 08:52

Combine the Rows into single row in SQL Server

1 Answers1