SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable (id INTEGER, name VARCHAR(10),type integer,type_name Varchar(10));
INSERT INTO mytable (id, name,type,type_name) VALUES
  (101, 'John',1,'cat'),
  (101, 'John',2,'dog'),
  (253, 'Bill',2,'dog'),
  (376,'Rick',3,'giraffe'),
  (376,'Rick',2,'dog'),
  (440,'James',2,'dog'),
  (440,'James',1,'cat'),
  (440,'James',3,'giraffe');

8 rows affected

select * from mytable;


id
name
type
type_name


101
John
1
cat

101
John
2
dog

253
Bill
2
dog

376
Rick
3
giraffe

376
Rick
2
dog

440
James
2
dog

440
James
1
cat

440
James
3
giraffe


Select 
         ID
       , name
       , case when count(1) > 1 then sum(type)+1 else sum(type) end as CombinedTypeId
       , String_agg(type_name,',') WITHIN GROUP (ORDER BY type) as newTypeName
from MyTable
  Group by 
         ID
       , name
  order by 1;


ID
name
CombinedTypeId
newTypeName


101
John
4
cat,dog

253
Bill
2
dog

376
Rick
6
dog,giraffe

440
James
7
cat,dog,giraffe

id	name	type	type_name
101	John	1	cat
101	John	2	dog
253	Bill	2	dog
376	Rick	3	giraffe
376	Rick	2	dog
440	James	2	dog
440	James	1	cat
440	James	3	giraffe

ID	name	CombinedTypeId	newTypeName
101	John	4	cat,dog
253	Bill	2	dog
376	Rick	6	dog,giraffe
440	James	7	cat,dog,giraffe