Our Training Programs

All contents are copyright of their authors.

Select multiple rows with matching id(s)

Select multiple rows with matching id(s)

Amit Makhija

7yAsked Dec 10, 2018, 6:32 AM1.1k

I tried for hours and read many posts but I still can't figure out how to handle this request:

I have a table like this:

roleid comid

11 A

12 A

11 B

12 B

13 B

I would like to select the roleid that occurs more than once with the different comid.

I wanna output should be something like:

roleid comid

11 A,B

12 A,B

13 B

Know the answer? Post it — somebody with the same question will find it here.

It is the same account you read, post and publish with — and you will come straight back to this page.

Code Alone7y ago
Thank you pritaeas for telling a good thing.
Jayesh Vaghasiya7y ago
try This 100%

create table #tmp(roleid int,comid varchar)
insert into #tmp values(11,'A'),(12,'A'),(11,'B'),(12,'B'),(13,'B')
SELECT roleid, comid=
STUFF((SELECT ', ' + comid
FROM #tmp TM where TM.roleid = TT.roleid
FOR XML PATH('')), 1, 2, '')
FROM #tmp TT
GROUP BY roleid
drop table #tmp
Ramesh Palanivel7y ago
Hi amit,

Please try the below script
SELECT roleid, comid=
    STUFF((SELECT ', ' + comid
           FROM yourtablename
          FOR XML PATH('')), 1, 2, '')
FROM yourtablename
GROUP BY roleid
Guest User7y ago
If you are using SQL Server 2017, then you can use: string_agg

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017