Create table table_1(col_id int,col_value varchar(20))

Create table table_2(col_id2 int,col_id int,col_value_2 varchar(20),col_status bit)

insert into table_1 values(1,'aaa')
insert into table_1 values(2,'bbb')
insert into table_1 values(3,'ccc')

insert into table_2 values(1,'xyz',1,0)
insert into table_2 values(2,'pqr',1,0)
insert into table_2 values(3,'fvg',1,1)

insert into table_2 values(1,'der',2,1)
insert into table_2 values(2,'tyu',2,0)
insert into table_2 values(3,'uji',2,0)

insert into table_2 values(1,'qwe',3,1)
insert into table_2 values(3,'iop',3,0)

col_value   col_value_2  col_value_2(where col_status=1)
aaa          xyz,pqr       fvg
.
.

quote:

---

Originally posted by bklr

select t.col_value, stuff((select ','+ col_value_2 from table_2 where col_id = t.col_id and col_status <> 1
for xml path('')),1,1,'')as val, col_value_2 a
from table_1 t
inner join table_2 s on s.col_id = t.col_id and col_status = 1

---

quote:

---

Originally posted by bklr

getting the col_value_2 values which are not equal to status 1 in the subquery for comma separted i am using xml path (see in books online for details)
for the status = 1 i used the inner join to get the values

---

insert into table_2 values(1,1,'xyz',0)
insert into table_2 values(2,1,'pqr',0)
insert into table_2 values(3,1,'fvg',1)

insert into table_2 values(4,2,'der',1)
insert into table_2 values(5,2,'tyu',0)
insert into table_2 values(6,2,'uji',0)

insert into table_2 values(7,3,'qwe',0)
insert into table_2 values(8,3,'iop',0)

col_value            val              a
-------------------- ----------------------
aaa                  xyz,pqr         fvg
bbb                  tyu,uji         der