# Thread: Comparing two row values

1. Registered User
Join Date
Apr 2005
Posts
4

## Unanswered: Comparing two row values

whats the best way to compare two row values and concatenate those two

For example

Table1
-------
Col1 Col2
----------
A 1
A 4
B 2
C 3
A 5

I need a query to return something like
"where (A=1 or A=4 or A=5) and (b=2) and (c=3)"

Last edited by raj48000; 01-15-07 at 17:06.

2. Registered User
Join Date
Feb 2003
Location
India
Posts
216
try using cursor. one single SQL will not do the job.

3. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
If I understand what you're asking, there is no need to concatenate, and certainly no need for a cursor.

What I think you want to do is SELECT rows from a table or view based on the values of four different columns (a, b, c, and d). In your case, you need to see the rows that meet all three criteria: Column A values of 1, 4, or 5 and also have Column B = 2 and Column c = d.

If I understand your question, then I'd use:
Code:
```SELECT *
FROM myTable
WHERE  (a = 1 OR a = 4 OR a = 5)
AND b = 2
AND c = d```
-PatP

4. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369
You need to explain more. The way I see it you want
Code:
```select * from Table1
where (Col1='A' and Col2 in (1,4,5))
or (Col1='B' and Col2=2)
or (Col1='C' and Col2=3)```

5. Registered User
Join Date
Apr 2005
Posts
4
Ok let me elaborate

I have a table with two columns with the values

Col1 Col2
----------
A 1
A 4
B 2
C 3
A 5

The data mentioned above is just the sample, I dunno wats its going to be in

I am looking for a query and that should form the where clause like
"where (A=1 or A=4 or A=5) and (b=2) and (c=3)"

which means it should find the identical ones first and concate with or clause and rest join with the and clause. In this case we have three A's so it should form (A=1 or A=4 or A=5) and then and clause to (b=2) and (c=3)

6. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
I think I get what you're after.

Where do you need to plug in this "where" clause when you're done? Perhaps there's a cleaner way to get the job done.

7. Registered User
Join Date
Feb 2003
Location
India
Posts
216
this only froms the string that u r looking for using cursor (i can hear some screaming!!). u know better what to do with that string

Code:
```declare @Str char(1)
declare @LastStr char(1)
declare @Num smallint
declare @Out varchar(200)
set @Out = '('
declare Cur1 cursor for select Str,Num from theTable order by str,num
open Cur1
fetch next from Cur1 into @Str,@Num
while @@fetch_status = 0
begin
if (@LastStr is not null)
begin
if(@LastStr <> @Str)
set @Out = @Out + ') and ('
else
set @Out = @Out + ' or '
end
set @Out = @Out + @Str + '='+ cast(@Num as varchar)
set @LastStr = @Str
fetch next from Cur1 into @Str,@Num
end
close Cur1
deallocate Cur1
set @Out = 'where ' + @Out + ')'
print @Out```

8. Registered User
Join Date
Apr 2005
Posts
4
thanks upalsen, but I am thinking is there any other way to do this without cursors. Your code definetly works but looking for alternative. Thanks

9. Registered User
Join Date
Feb 2003
Location
India
Posts
216
as i told earlier, it is possible without cursor as well. but that is neither going to look smarter not would perform better. performance of course is dependent on many other factors that is not clear to us form the post. if it is a onetime affair u can use cursor without any doubt. however, if u r still interested in cursor-less sql, let me know.

10. Registered User
Join Date
Apr 2005
Posts
4
thanks upalsen, yes please let me know how can we do this without cursors, I did use the cursors and its working fine, but just want to know how can we do without cursors. I appreciate your help.

11. Registered User
Join Date
Feb 2003
Location
India
Posts
216
Code:
```select Col1,Col2 into #TT from theTable order by Col1,Col2
update #TT set #TT.Col1 = #TT.Col1+'_Max' /*expecting the width sufficient to hold 5 chars, else add col/change width*/ from #TT, (select Col1,max(Col2) as MaxCol2 from #TT group by Col1) as TT2 where #TT.Col1 = TT2.Col1 and #TT.Col2=TT2.MaxCol2
declare @sql varchar(200)
set @sql = '('
select @sql = @sql + left(Col1,1) + '=' + cast(Col2 as varchar) + case when len(Col1)>1 then ') and (' else ' or ' end from #TT
set @sql = 'where ' + left(@sql,len(@sql)-6)
print @sql
drop table #TT```
looks meaningless though...cant find anything better than this.....
Last edited by upalsen; 01-19-07 at 01:10.

12. Registered User
Join Date
Mar 2005
Location
Netherlands
Posts
280
I like a challenge. I came up with this one...
Code:
```IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
CREATE TABLE #tmp (Col1 CHAR(1), Col2 CHAR(1))
INSERT #tmp VALUES ('A','1')
INSERT #tmp VALUES ('A','4')
INSERT #tmp VALUES ('B','2')
INSERT #tmp VALUES ('C','3')
INSERT #tmp VALUES ('A','5')
INSERT #tmp VALUES ('C','1')

DECLARE @sql VARCHAR(1000)
SET @sql = '('

SELECT @sql = @sql + CASE
WHEN Col1 IS NULL THEN ''
WHEN Col2 IS NULL THEN ') AND ('
ELSE Col1 + '=' + Col2 + ' OR '
END
FROM   #tmp
GROUP BY Col1, Col2
WITH ROLLUP

SELECT REPLACE(LEFT(@sql, LEN(@sql) - 6), ' OR )', ')')```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•