Group and tally values for each record in SQL

Question

Im trying to run a select statement to group records having similar IDs but also tally the values from another column for each master ID. So for example below. The result for each line will be the first instance unique ID and the 2 names shown from each record separated by semi colon. Thanks in advance.

Current set

ID                               Name              Cnt
-------------------------------- ----------------- ---
0001D72BA5F664BE129B6AB5744E2BE0 Talati, Shilpa    1
0001D72BA5F664BE129B6AB5744E2BE0 Weaver, Larry     1
0007EAB7CE9A3F2F95D2D63D0BBD08A9 St-Hilaire, Edith 1
0007EAB7CE9A3F2F95D2D63D0BBD08A9 Talati, Shilpa    1

Result:

0001D72BA5F664BE129B6AB5744E2BE0 Talati, Shilpa; Weaver, Larry

What database are you using? Why does the result only have one row instead of two? — Gordon Linoff, May 09 '14 at 23:33

score 0 · Answer 1 · answered May 09 '14 at 23:36

0

The easiest way to solve this in SQL Server is:

select masterId, min(name) + '; ' + max(name)
from table t
group by masterId;

answered May 09 '14 at 23:36

Gordon Linoff

1,242,037
58
646
786

Thank you but I have some IDS that exist more than 2 times and its not grabbing all the name values. – user3622204 May 09 '14 at 23:43
@user3622204 . . . Your question is explicitly: "2 names shown from each record separated by semi colon." More than 2 names and you have the general string aggregation problem in SQL Server. I would suggest you search for that rather than asking another question. – Gordon Linoff May 09 '14 at 23:44

score 0 · Answer 2 · answered May 10 '14 at 00:58

Here's one way using a recursive common table expression. Given a table like this:

create table dbo.Fizzbuzz
(
  id       int         not null identity(1,1) primary key clustered ,
  group_id int         not null ,
  name     varchar(50) not null ,
  cnt      int         not null ,
)

containing this data

id group_id name   cnt
-- -------- ------ ---
1  1        Bob      3
2  1        Carol    5
3  1        Ted      6
4  1        Alice   16
5  2        Harold  72
6  2        Maude   28

This query

with recursive_cte as
(
  select group_id = t.group_id ,
         row      = t.row      ,
         name     = convert(varchar(8000),t.name) ,
         cnt      = t.cnt
  from ( select * ,
                row = row_number() over (
                        partition by group_id
                        order by id
                        )
         from dbo.Fizzbuzz
       ) t
  where t.row = 1
  UNION ALL
  select group_id = prv.group_id ,
         row      = nxt.row      ,
         name     = convert(varchar(8000), prv.name + ' and ' + nxt.name ) ,
         cnt      = prv.cnt  + nxt.cnt
  from recursive_cte prv
  join ( select * ,
                row = row_number() over (
                        partition by group_id
                        order by id
                        )
         from dbo.Fizzbuzz
       ) nxt on nxt.group_id = prv.group_id
            and nxt.row      = prv.row + 1
)
select group_id = t.group_id ,
       total    = t.cnt      ,
       names    = t.name
from ( select * ,
              rank = rank() over (
                       partition by group_id
                       order by row desc
                     )
       from recursive_cte
     ) t
where rank = 1
order by group_id

produces the following output

group_id cnt name
-------- --- -------------------------------
    1     30 Bob and Carol and Ted and Alice
    2    100 Harold and Maude

One should note however, that the depth of recursion is bounded in SQL Server.

score 0 · Answer 3 · answered May 10 '14 at 02:36

0

SELECT  
      t1.ID, 
      (SELECT Name + '; ' 
       FROM yourtable t2 
       WHERE t1.ID = t2.ID 
       for xml path('')) as Name
FROM yourtable t1 
GROUP BY t1.ID

answered May 10 '14 at 02:36

t-clausen.dk

43,517
12
59
92

Group and tally values for each record in SQL

3 Answers3

Linked