SQL - STUFF Function

I am trying to put the FirstName and EmailAddress in one column.  For each role I want to see the Users that are assigned to that role.

I have the below code.  I receive data but there are 16 names for "Received" in separate rows and I want ALL of the 16 names in 1 column.

SELECT 
SR.id_roles,
CASE 
	WHEN SR.id_roles = 5 THEN
		'Received'
	WHEN SR.id_roles = 6 THEN
		'Repaired and Closed'
	WHEN SR.id_roles = 12 OR SR.id_roles = 13 THEN
		'Approved'
	WHEN SR.id_roles = 14 THEN
		'Closed'
	END AS Roles,
STUFF((SELECT '; ' + BU.firstName + ' (' + BU.email_user + ')'
          FROM BASE_User BU 
          WHERE BU.id_user = SRU.id_user
          FOR XML PATH('')), 1, 1, '') AS [FoundUsers]
FROM SYS_Roles SR
INNER JOIN SYS_RolesUser SRU ON SR.id_roles = SRU.id_roles
WHERE SR.id_roles IN (5, 6, 112, 13, 14)

Open in new window


The STUFF Function works.  I tested it alone without the GROUP BY in the STUFF Function.

I tried adding GROUP BY SR.id_roles at the end of the SQL and I receive an error -> Column 'SYS_RolesUser.id_user' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
LVL 2
CipherISAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
Without seeing a table layout, best guess is to move the INNER JOIN on SYS_RolesUser from the outer query to the inner query and update the inner join's WHERE clause.
0
CipherISAuthor Commented:
Huh?
0
Shaun KlineLead Software EngineerCommented:
You have a table of roles (SYS_Roles), a table of users (BASE_User), and a table (SYS_RolesUser) that joins the two together. Your query should look like this:
SELECT 
SR.id_roles,
CASE 
	WHEN SR.id_roles = 5 THEN
		'Received'
	WHEN SR.id_roles = 6 THEN
		'Repaired and Closed'
	WHEN SR.id_roles = 12 OR SR.id_roles = 13 THEN
		'Approved'
	WHEN SR.id_roles = 14 THEN
		'Closed'
	END AS Roles,
STUFF((SELECT '; ' + BU.firstName + ' (' + BU.email_user + ')'
          FROM BASE_User BU 
		  INNER JOIN SYS_RolesUser SRU ON BU.id_user = SRU.id_user
          WHERE SR.id_roles = SRU.id_roles
          FOR XML PATH('')), 1, 1, '') AS [FoundUsers]
FROM SYS_Roles SR
WHERE SR.id_roles IN (5, 6, 112, 13, 14)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CipherISAuthor Commented:
Ah, it took a sec to understand.  This works.

SELECT 
SR.id_roles,
CASE 
	WHEN SR.id_roles = 5 THEN
		'Received'
	WHEN SR.id_roles = 6 THEN
		'Repaired and Closed'
	WHEN SR.id_roles = 12 OR SR.id_roles = 13 THEN
		'Approved'
	WHEN SR.id_roles = 14 THEN
		'Closed'
	END AS Roles,
[RMAUsers] = LTRIM(STUFF((SELECT '; ' + BU.firstName + ' (' + BU.email_user + ')'
						  FROM BASE_User BU
						  INNER JOIN SYS_RolesUser SRU ON SR.id_roles = SRU.id_roles 
						  WHERE BU.id_user = SRU.id_user
						  FOR XML PATH('')), 1, 1, ' '))
FROM SYS_Roles SR
WHERE SR.id_roles IN (5, 6, 112, 13, 14)
GROUP BY SR.id_roles

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.