Select id with multiple values in one row

Question

I have two tables:

SocietyType:

IdSociety	IdTypeSociety
1	1
1	2
1	3
2	1

And TypeSociety

IdTypeSociety	TypeName
1	Type1
2	Type2
3	Type3

I want to have these result:

IdSociety	TypeName
1	Type1 , Type2, Type3
2	Type1

IdTypeSociety its foreignkey to TypeSociete

I want to regroup the multiple value to one id in one row, if someone can help me please, thank you.

Answer 1

For SQL Server versions, prior to SQL Server 2017, you can use FOR XML PATH base concatenation.

Thanks to @Critical Error, for the table creation scripts

DECLARE @SocietyType table (
    IdSociety int, IdTypeSociety int
);
INSERT INTO @SocietyType VALUES
    ( 1, 1 ), ( 1, 2 ), ( 1, 3 ), ( 2, 1 );

DECLARE @TypeSociety table (
    IdTypeSociety int, TypeName varchar(10)
);
INSERT INTO @TypeSociety VALUES
    ( 1, 'Type1' ), ( 2, 'Type2' ), ( 3, 'Type3' );


SELECT distinct st.IdSociety, 
STUFF(((
SELECT ',' + ts.Typename from @TypeSociety as ts 
INNER JOIN @SocietyType as ist ON ist.IdTypeSociety = ts.IdTypeSociety
where ist.IdSociety  = st.IdSociety
FOR XML PATH(''), TYPE).value('text()[1]','nvarchar(max)')),1,1,'') AS TypeName
FROM @SocietyType AS st

IdSociety	TypeName
1	Type1,Type2,Type3
2	Type1