将不同行的字符数据横向连接

DECLARE @roles NVARCHAR(255);
SELECT @roles = ISNULL(@roles + ',','') + r.RoleName FROM Physical.Roles r INNER JOIN Physical.UsersInRoles ur ON r.Id=ur.RoleId WHERE ur.UserId=@userId;
SELECT @roles

输出结果示例：Admin,User

 

附：表结构

CREATE TABLE Roles
(
  Id INT NOT NULL ,
  RoleName NVARCHAR(20),
  CONSTRAINT UX_Roles_RoleName UNIQUE NONCLUSTERED   ([RoleName] ASC )
)

CREATE TABLE UsersInRoles
(
 UserId UNIQUEIDENTIFIER NOT NULL,
 RoleId INT NOT NULL
)