SQL 多行拼成一行 (3种解决方案)

原数据：

 UserID      RoleName          RoleID  ----------- ----------       --------  2014000     developer           1  2014000     product             2  2014001     developer           1  2014002     developer           1  2014002     sales               3  

结果：UserID      NewRoleName                NewRoleID----------- ------------------        ------------ 2014000     developer, product          1|22014001     developer                   12014002     developer, sales            1|3

解决方法1：

SELECT t.UserID      ,STUFF((SELECT ','+ltrim(RoleName)                      FROM TEST                        WHERE UserID=t.UserID FOR XML PATH('')), 1, 1, '')       AS NewRoleName      ,STUFF((SELECT '|'+ltrim(RoleID)                      FROM TEST                        WHERE UserID=t.UserID FOR XML PATH('')), 1, 1, '')       AS NewRoleID  FROM TEST t  GROUP BY UserID  

解决方法2：

SELECT A.*      ,STUFF(CONVERT(VARCHAR(100),C.RoleID),1,1,'') AS NewRoleID      ,REPLACE(          STUFF(CONVERT(VARCHAR(100),C.RoleName),1,1,'')          ,'|',', ') AS NewRoleName  FROM (       SELECT DISTINCT UserID          -- ,COUNT(DISTINCT ID) AS CountOfID       FROM TEST       GROUP BY UserID  ) A  CROSS APPLY (       SELECT RoleID = (          SELECT '|' + Convert(varchar(10),RoleID)          FROM TEST B          WHERE B.UserID = A.UserID          FOR XML PATH(''), TYPE         ),          RoleName = (          SELECT '|' + Convert(varchar(10),RoleName)          FROM TEST B          WHERE B.UserID = A.UserID          FOR XML PATH(''), TYPE         )  ) C 

解决方法3：

if object_id('F_RoleName') is not null       drop function F_RoleName   go   create function F_RoleName(@UserID VARCHAR(100))   returns nvarchar(100)   as   begin       declare @S nvarchar(100)       select @S=isnull(@S+', ','')+ RoleName from TEST where UserID=@UserID       return @S   end   go 

1. Select distinct UserID  
2.     ,NewRoleName=dbo.F_RoleName(UserID)   
3.     ,NewRoleID=dbo.F_RoleID(UserID)   
4. from TEST