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.

Does this answer your question? [How to use GROUP BY to concatenate strings in SQL Server?](https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — Charlieface, Apr 23 '21 at 04:23

score 3 · Answer 1 · edited Apr 23 '21 at 00:11

Depending on your version of SQL Server (17+) you can use STRING_AGG.

Here's an example:

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
    s.IdSociety,
    STRING_AGG ( t.TypeName, ',' ) AS TypeName
FROM @SocietyType AS s
INNER JOIN @TypeSociety AS t
    ON s.IdTypeSociety = t.IdTypeSociety
GROUP BY
    IdSociety;

RETURNS

+-----------+-------------------+
| IdSociety |     TypeName      |
+-----------+-------------------+
|         1 | Type1,Type2,Type3 |
|         2 | Type1             |
+-----------+-------------------+

Venkataraman R · Accepted Answer · 2021-04-23T04:37:28.083

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

`FOR XML PATH(''), TYPE).value('text()[1]','nvarchar(max)'),1,1,'')` to avoid XML-escaping problems — Charlieface, Apr 23 '21 at 04:25

Select id with multiple values in one row

2 Answers2