How do I group this column using T-SQL?

[Domo]

I want to group the output by tblgroup.group_name (not aliased). Done some reading and I need to add a count into the SELECT statement. Tried and get errors, likely because of the join.
All I want to see is a list of names for each group. Help welcome!

SELECT tblgroup.group_name AS [Group Name], concat(tblUser.user_first_name,' ',tblUser.user_last_name) AS Name
FROM tblXGroupsUsers LEFT OUTER JOIN
tblGroup ON tblXGroupsUsers.group_id = tblGroup.group_id LEFT OUTER JOIN
tblUser ON tblXGroupsUsers.user_id = tblUser.user_id

 

[Domo]

Yeah that's where I got to. But as soon as I add a group at the end SQL throws a wobbly:
Msg 8120, Level 16, State 1, Line 1
Column 'tblUser.user_first_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'tblUser.user_last_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

 

[Domo]

That's the main issue. No reporting engine. It's a limitation in the application which prevents us from displaying all users by groups. So tried to tackle it via SSMS.

 

[Domo]

Thanks tom_e. I'll obviously be doing the same!

 

[Domo]

Makes sense. Although I was hoping for something like:

Group1

Adam Anderson
Alan Atkinson
Anna Attwell​

Group2

James Smith
Ben Bailey
Simon Forde
​

They can export to CSV and group in Excel