0
UserId  FirstName   LastName    Role
    34      Rahul       Gupta       Manager
    34      Rahul       Gupta       Director

I have one stored Procedure which returns me result like this, for one record if I have two roles I get two separate rows. I want to have only one row like this: 

UserId  FirstName   LastName    Role
34      Rahul       Gupta       Manager,Director

Stored Procedure is :

SELECT [USER].[USER_ID] AS [UserId]
      ,[FIRST_NM] AS FirstName
      ,[LST_NM] AS LastName
      ,[Role].[ROLE_ID] AS RoleId
      ,[Role].[ROLE_NM] AS Role
FROM [dbo].[USER] [User] (NOLOCK)
INNER JOIN dbo.USER_ROLE [USER_ROLE] on [User].USER_ID=[USER_ROLE].USER_ID
INNER JOIN dbo.ROLE [Role] on [Role].ROLE_ID=[USER_ROLE].ROLE_ID
WHERE [USER].[SSO_ID] = 'xyz'

This is not working..what i am doing wrong here 

SELECT [USER].[USER_ID] [UserId]    
       ,[FIRST_NM] AS FirstName  
      ,[LST_NM] AS LastName        
        , (STUFF((SELECT CAST(', ' + [Role].[ROLE_NM] AS VARCHAR(MAX)) 

FROM [dbo].[USER] [User] (NOLOCK)       
INNER JOIN dbo.USER_ROLE [USER_ROLE] on [User].USER_ID=[USER_ROLE].USER_ID        
INNER JOIN dbo.ROLE [Role] on [Role].ROLE_ID=[USER_ROLE].ROLE_ID     
WHERE [USER].[SSO_ID] = 'a40ecdebfef84f099f74c4d9cf537929'  
FOR XML PATH ('')), 1, 2, '')) AS Roles
Bokambo
  • 4,204
  • 27
  • 79
  • 130
  • 1
    If this is a SQL Server specific question, it's good that you tag that as well. – sstan Jun 17 '15 at 13:42
  • 2
    possible duplicate of [Is there a way to create a SQL Server function to "join" multiple rows from a subquery into a single delimited field?](http://stackoverflow.com/questions/6899/is-there-a-way-to-create-a-sql-server-function-to-join-multiple-rows-from-a-su) – Donal Jun 17 '15 at 13:46
  • I hope you also know the risks of using NOLOCK. – James Z Jun 17 '15 at 14:11
  • Can you please explain risk if using NOLOCK ? – Bokambo Jun 17 '15 at 14:12
  • Here's a link that might help. http://bit.ly/1HTaQPo – Bill Gregg Jun 17 '15 at 14:15
  • This is not working i have modified...can you please tell what i am doing wrong here ...SELECT [USER].[USER_ID] [UserId] ,[FIRST_NM] AS FirstName ,[LST_NM] AS LastName , (STUFF((SELECT CAST(', ' + [Role].[ROLE_NM] AS VARCHAR(MAX)) FROM [dbo].[USER] [User] (NOLOCK) INNER JOIN dbo.USER_ROLE [USER_ROLE] on [User].USER_ID=[USER_ROLE].USER_ID INNER JOIN dbo.ROLE [Role] on [Role].ROLE_ID=[USER_ROLE].ROLE_ID WHERE [USER].[SSO_ID] = 'a40ecdebfef84f099f74c4d9cf537929' FOR XML PATH ('')), 1, 2, '')) AS Roles – Bokambo Jun 17 '15 at 14:18
  • You should ask a different question. – Bill Gregg Jun 17 '15 at 14:23

0 Answers0