Help with Query To combine results from two Columns

[vineetbindal]

Hi All.

I have two Columns column1 and column2. i have to run a query with some value from colum1 depending on it will select result from coloumn2 and if that result is present in coloumn 1 it will again select something from coloum2 again if that new result is present in coloum 1 it will select something from colum2 . i have to combine all this in one column without repeating values.

something like.

Column1 Column2 Column3

2100 2200 V
2200 2307 V
2200 2308 M
2307 2400 V

now if i run my query where column3='V'

then the result should be

2100
2200
2307
2400

values from both the columns in one column(i may skip the first value 2100) becouse i allready know it

please help

[ck9663]

Looks like a CTE would be your option

--- CK

[vineetbindal]

can you give me a little example or something please ?

[Uncle Dickie]

I'm not really sure what you are after but you can get the result set you want in your example with a UNION statement:

SELECT    Column1 FROM table WHERE Column3 = 'V'
UNION
SELECT    Column2 FROM table WHERE Column3 = 'V'

[vineetbindal]

no that wont help me.

Actually we have a number of starting point followed by a chain in those columns for ex/;
2100 2200 V
2105 2200 V
2106 2200 V
2100 2205 M
2200 2307 V
2200 2308 M
2106 2800 M
2307 2400 V
2200 2100 M
2200 2106 M

the union will give me 2100,2105,2106, 2307,2200,2400

but i wanna get 2100,2200,2307, 2400
for ex: my query starts with where cl1=2100 ans cl3=V

then it will go to cl1 select 2100 and check from cl2 and select 2200 and becouse 2200 is also in cl1 and for that cl3='V' it will select 2307 and then 2400 so result will be 2100,2200, 2307,2400

please help..

[ck9663]

Search SQL Server Common Table Expression in google.

--- CK

[vineetbindal]

yeah i did that. tnx a lot, that was very useful

[kunalsmehta]

Hi, try this

declare @table table (col1 int, col2 int ,col3 varchar)
insert into @table values (2100, 2200, 'V')
insert into @table values (2200, 2307, 'V')
insert into @table values (2200, 2308, 'm')
insert into @table values (2307, 2400, 'V')
insert into @table values (2400, 2500, 'V')
insert into @table values (2600, 2700, 'V')
insert into @table values (2700, 2800, 'V')
select col1 from @table where col3='v'
union
select col2 from @table where col2 in (select col1 from @table where col3='v')

Kunal

[vineetbindal]

Tnx a lot kunal , its work. i tried the similer approach, but with a join as they have given in CTE in msdn.