SQL Server Group by with multiple table getting 3 rows, when only want 1

Question

I am using the following SQL code, where the Id and DateTime columns are the same and I want to group them together. The Ref is different and in this example I have 3 names with the same Id and DateTime. I want to get one row of data output like this:

Id       DateTime                 Ref
---------------------------------------------
3        Sep 4 2021 08:00:00    Billy Joel
                                Chris Farley
                                Joe Blow

Instead I'm getting 3 rows of data:

 Id       DateTime                Ref
---------------------------------------------
3        Sep 4 2021 08:00:00    Billy Joel
3        Sep 4 2021 08:00:00    Chris Farley
3        Sep 4 2021 08:00:00    Joe Blow

Below is a my SQL code and sqltest site with my data and SQL query. Can anyone help?

Select 
    a.Id, a.DateTime, 
    (o.cFName + ' ' + o.cLName) as Ref 
From 
    GameAssignment g 
Left Outer Join 
    games a on g.gameId = a.Id 
Left Outer Join 
    FieldGym f On a.FieldGym = f.Id 
Left Outer Join 
    Official o On o.Id = g.OfficialId
Where 
    Convert(date, a.DateTime) = '09/04/2021' 
    And f.Id = 3
Group by 
    a.Id, o.cFName, o.cLName, a.DateTime 
Order by 
    a.DateTime

SQL test site https://sqltest.net/#1422359

What version of SQL Server are you using? And do the values in Ref need to be in a specific order? — squillman, Sep 02 '21 at 15:13
That's not the way to think about SQL. SQL returns relations (or relvars), which are "tabular". Hiding repeated information is a presentation layer concern. There are ways you can force SQL to produce "formatted" jagged output, but you *should not*. — allmhuran, Sep 02 '21 at 15:14
sql server 2019, and yes Ref should be ordered by position. which I forgot to put on the query. a.DateTime, g.Postion would be order — mlg74, Sep 02 '21 at 15:18
allmhuran, i understand what you are saying, I just don't want repeated data, but maybe i need to rethink my approach — mlg74, Sep 02 '21 at 15:20
I use this approach for set up to presentation layer (specifically SSRS but any). google for xml path(''). I typically set my delimeter to a pipe ('|') and then replace in SSRS with Environment.NewLine — KeithL, Sep 02 '21 at 15:21
Hello, You should look at https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server Group by isn't meant to group different rows in a single line. It will just regroup rows which have all the same values in the selected columns. Here it isn't the case as you've got different ref. — Kirjava, Sep 02 '21 at 15:26
what is your expected output? will name show in comma separated or 3 row where date and id will empty for row 2 and 3. — Rahul Biswas, Sep 02 '21 at 15:29
Let's put that a different way. How are your users going to look at this information? In excel? In an SSRS report? In a web page? In a native desktop or mobile application? — allmhuran, Sep 02 '21 at 15:57
Does this answer your question? [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Charlieface, Sep 02 '21 at 20:07

score 0 · Answer 1 · answered Sep 02 '21 at 15:19

0

With SQL Server it seems your closest option is STRING_AGG() function. Is this good enough for you?

answered Sep 02 '21 at 15:19

Eldar S

579
3
17

1

Did you read the explanation in the link? Try it out on your database – Eldar S Sep 02 '21 at 15:24

score 0 · Answer 2 · answered Sep 02 '21 at 15:33

Amended you code to use a for xml path solution:

Select 
    a.Id, a.DateTime, 
    STUFF((select '|' + o.cFName + ' ' + o.cLName
          from Official o 
          where o.Id = g.OfficialId
          order by o.cFName,o.cLName
          for xml path(''))
          ,1,1,'')
              as Ref 
From 
    GameAssignment g 
Left Outer Join 
    games a on g.gameId = a.Id 
Left Outer Join 
    FieldGym f On a.FieldGym = f.Id 
  
Where 
    Convert(date, a.DateTime) = '09/04/2021' 
    And f.Id = 3
Group by 
    a.Id, a.DateTime 
Order by 
    a.DateTime

This will give you a pipe delimited list in the column, you can chose something else or replace in the presentation layer with vbCRLF or environment.NewLine.

Give it a try.

Rahul Biswas · Answer 3 · 2021-09-02T16:19:26.327

As name will show in separate row so lag() is used to retrieve the previous id. If id and prev_id is same then id and date will empty string. Basically it is handled in reporting tool. Most of cases name will be shown as comma separated. If Null required both id and datetime then use NULL instead of empty string.

-- SQL Server
SELECT CASE WHEN t.id = t.prev_val THEN '' ELSE CAST(t.id AS varchar(10)) END id
     , CASE WHEN t.id = t.prev_val THEN '' ELSE CONVERT(VARCHAR, t.DateTime, 109) END DateTime
     , t.Ref
FROM (Select 
    a.Id, a.DateTime, 
    (o.cFName + ' ' + o.cLName) as Ref,
    LAG(a.Id) OVER (Order by a.DateTime) prev_val
From 
    GameAssignment g 
Left Outer Join 
    games a on g.gameId = a.Id 
Left Outer Join 
    FieldGym f On a.FieldGym = f.Id 
Left Outer Join 
    Official o On o.Id = g.OfficialId
Where 
    Convert(date, a.DateTime) = '09/04/2021' 
    And f.Id = 3
Group by 
    a.Id, o.cFName, o.cLName, a.DateTime) t 
Order by t.DateTime

Please check from url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=8530d66b175704a2ef6259a1021d46aa

@mlg74 please check this query and let me know if any query regard this. — Rahul Biswas, Sep 02 '21 at 15:56

SQL Server Group by with multiple table getting 3 rows, when only want 1

3 Answers3