SQL Server - Distinct

Question

This is my table:

I want to know which names exist more than once with the source "comp" and the source "manual".

So in this case I want the output to be: host3 zyx (name and group) because the name host3 exists more than once and it has the source manual and the source comp.

I've tried this (SQL Server):

SELECT name, group 
FROM table
GROUP BY name
HAVING (COUNT(name) > 1) and ????

Answer 1

As I understand you want something like

SELECT name, max([group]) -- or STRING_AGG([group],',') 
FROM table
WHERE source in ('comp','manual')
GROUP BY name
HAVING COUNT(DISTINCT source) > 1

or you have to group by (in most sql dialects) group, too

SELECT name, [group]  
FROM table
WHERE source in ('comp','manual')
GROUP BY name, [group]
HAVING COUNT(DISTINCT source) > 1