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

 

[AHarvey]

if you'd want to see a list of names per group you'd use DISTINCT on the name

Count will just show you how many of each.

Then use and order by

EDIT: I'd also say to format your sql better to make it easier to read:
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​

 

[AHarvey]

Scratch that, you'd actually need to use GROUP BY XXX at the end to group them

 

[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.

 

[tom_e]

You need to group by group name and then the concatinated string fields as well as they are not aggregated and therefore sql doesn't know which ones to pick to display alongside the grouped by group name.

Thinking about it there's no need to group it, you'll have a group name column and a user's column. If you want a list where it shows one group name then the list of users then the next group name and a list of users you'd do that in a grouping on your front end reporting package.

 

[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.

 

[tom_e]

I'm pretty sure it's not possible in pure sql, if I get five minutes in the office tomorrow I'll have a play around with some of our data.

 

[Domo]

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

 

[touch]

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.

If you want to display all users by groups, you need to ORDER BY, not GROUP BY.

GROUP BY would be used for an output like:


ORDER BY would be used for an output like:

 

[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

 

[Ergates]

What you're looking for is the MS-SQL equivalent of BREAK in Oracle.

Unfortunately, there isn't one.

 

[davetherave2]

Ok I think I have managed to get your desired results based on some sample code I knocked up.

declare @table table
(
 
 groupname nvarchar(max),
 name nvarchar(max) ,
 depth int
)


insert into @table
values(
 'Group1', 'Group1',1
)
insert into @table
values(
 'Group1', 'A', 2
)
insert into @table
values(
 'Group1', 'B',2
)
insert into @table
values(
 'Group1', 'C', 2
)
insert into @table
values(
 'Group1', 'D', 2
)
insert into @table
values(
 'Group2', 'Group2', 1
)
insert into @table
values(
 'Group2', 'E',2
)
insert into @table
values(
 'Group2', 'F', 2
)
insert into @table
values(
 'Group2', 'G', 2
)
insert into @table
values(
 'Group2', 'H', 2
)




SELECT

 case when GroupName = Name then GroupName else '' end as [GroupName],
 case when GroupName != Name then Name else '' end  as [Name]

FROM
(select * from @table) t1
PIVOT
(
    Min(depth)
    FOR depth IN ([1],[2])
   
) AS pivotTable

ORDER BY
 (pivotTable.groupname + CASE WHEN pivottable.groupname = pivotTable.name then '' else pivotTable.name end ) asc

So what you would do is get your "group headings" and then join your "names together" and then set the depth level so if you had more than 2 you could then break it down further if you wanted. But lets keep it simple to two levels.

The important bit is using the PIVOT feature within MS-SQL, This is the value we are interested in as it determines how the data is broken out.

Once we have that we then do a case statement to show the appropriate value/non-value in the fields and then a fun order by statement to get the values in the right place

Hopefully you can follow this. If not I can expand further if it's not entirely clear as it does look a bit mental at first glance.