SQL Server Row based grouping

Question

Given the following setup:

DECLARE @TemplateInfo table (Name varchar(10), aRow int, aCol int, Value  int)

insert INTO @TemplateInfo (Name, aRow, aCol, Value)
VALUES ('A', 0,0,1),('B', 0,0,2),('A',1,0,2),('B',1,0,1),('C',0,0,1),('C',1,0,1)

How can I select the unique Name name values that have (aRow=0, aCol=0, Value=1) AND (aRow=1, aCol=0, Value=1).

I tried a query like this:

select  Name
from    @TemplateInfo info
where   (info.aRow = 0 and info.aCol = 0 AND Value = 1) 
        or (info.aRow = 1 and info.aCol = 0 AND Value = 1)
GROUP BY Name

But that returns A, B and C. How can I check to make sure there are matches across rows (only return C)

Answer 1

You can even use Intersect

Select name From @TemplateInfo where aRow=0 and  aCol=0 and Value=1
intersect
Select name From @TemplateInfo where aRow=1 and  aCol=0 and Value=1

Result

name
C

Answer 2

You were really close. Since you want to have both conditions evaluated yo could use HAVING:

select  Name
from    @TemplateInfo info
where   (info.aRow = 0 and info.aCol = 0 AND Value = 1) 
        or (info.aRow = 1 and info.aCol = 0 AND Value = 1)
group by Name
HAVING COUNT(*) = 2

Though this is valid only if there can be just one combination of aRow, aCol, Value for each name.