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)"

Thanks in advancetry using cursor. one single SQL will not do the job.|||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:SELECT *
FROM myTable
WHERE (a = 1 OR a = 4 OR a = 5)
AND b = 2
AND c = d-PatP|||You need to explain more. The way I see it you want
select * from Table1
where (Col1='A' and Col2 in (1,4,5))
or (Col1='B' and Col2=2)
or (Col1='C' and Col2=3)|||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)

Thanks in advance|||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.|||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

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|||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|||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.|||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.

Thanks in advance.|||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....|||I like a challenge. I came up with this one...
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 )', ')')