2 Replies - 11447 Views - Last Post: 27 February 2012 - 08:01 AM

#1 falcon00   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 56
  • Joined: 23-April 09

Looping through unormalized table with no cursor

Posted 22 February 2012 - 02:14 PM

I have an unormalized table with the following structure:
Row Key,pair_key,value_1,value_2
1,1,,value
2,1,value
3,1,value
4,1,value
5,2,value
6,2,value
7,2,value
8,3,,value
9,3,value
10,3,value




value_1 and value_2 are mutually exclusive. I need to read throught this table and within a pair_key only return rows where they are ALL value_1. So in this case I'd want a result of 2. I wrote something that uses a cursor but it's slow as heck. Can someone provide an alternative solution?

This post has been edited by falcon00: 22 February 2012 - 02:21 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Looping through unormalized table with no cursor

#2 Ionut   User is offline

  • D.I.C Lover
  • member icon

Reputation: 386
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Looping through unormalized table with no cursor

Posted 26 February 2012 - 08:22 PM

This is all I can think now.
select COUNT(*) from 
(
	select 
		COUNT(*) as CountPerPairKey
	from 
		Table1 t
	group by t.pair_key
	having COUNT(t.pair_key) = (select count(value_1) from Table1 where value_1 <> '' and pair_key = t.pair_key) 
 ) X


Was This Post Helpful? 0
  • +
  • -

#3 falcon00   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 56
  • Joined: 23-April 09

Re: Looping through unormalized table with no cursor

Posted 27 February 2012 - 08:01 AM

Here is the solution:
select Pair_Key
from @myTable
GROUP BY Pair_Key
HAVING  MAX(case when NULLIF(Value_2,'') Is NOT NULL then 1 else 0 end) = 0


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1