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)

Lamak · Accepted Answer · 2012-10-17 21:33:09Z

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.

answered Oct 17, 2012 at 21:33

Lamak

70.8k12 gold badges118 silver badges119 bronze badges

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

2 Comments

RThomas Over a year ago

+1, also here's a sql fiddle of your approach... I was working on something similar but you beat me too it :) sqlfiddle.com/#!3/4d781/3

Lamak Over a year ago

@RThomas - Thank you for the sql fiddle.

Niladri Biswas · Accepted Answer · 2012-10-18 04:44:37Z

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

answered Oct 18, 2012 at 4:44

Niladri Biswas

4,1712 gold badges19 silver badges24 bronze badges

Collectives™ on Stack Overflow

SQL Server Row based grouping

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related