Select multiple rows with matching id(s)

Question

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     |
+------+------+

Ilyes · Accepted Answer · 2018-12-10 12:32:30Z

1

Simply, you can use STUFF() function with FOR XML PATH('') as

SELECT roleid,
       STUFF(
         (
           SELECT ',' + comid
           FROM T
           WHERE roleid = T1.roleid
           FOR XML PATH('')
         ), 1, 1, ''
       ) comid 
FROM T T1
GROUP BY roleid

Demo

answered Dec 10, 2018 at 12:32

Ilyes

15k5 gold badges31 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michał Turczyn · Accepted Answer · 2018-12-10 12:40:05Z

1

If you are using SQL Server version greater than or equal to 2017, you can use also STRING_AGG function and to take only those roleids having more than one distinct comid you have to use HAVING caluse:

SELECT roleid,
       STRING_AGG(comid, ',')
FROM T
GROUP BY roleid
HAVING COUNT(DISTINCT comid) > 1

answered Dec 10, 2018 at 12:40

Michał Turczyn

41.3k18 gold badges58 silver badges87 bronze badges

1 Comment

AmIT Over a year ago

I'm using SQL Server 2017 but the error is occur i.e. 'STRING_AGG' is not a recognized built-in function name.

Collectives™ on Stack Overflow

Select multiple rows with matching id(s)

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related