sql server - SQL combining two row results into one row -

my ddls

create table employee (employeeid varchar(10) not null primary key, fname varchar(20) not null, mname varchar(20) not null, lname varchar(20) not null, dob date, position varchar(15)not null, addres varchar(40)not null, warehouseid varchar(10)foreign key references warehouse(warehouseid), retailshopid varchar(10)foreign key references retailshop(retailshopid), customerid varchar(10)foreign key references customer(customerid), );  create table warehouse_province (provinceid varchar(10) not null primary key, province varchar (25) not null, city varchar (25) not null, addresss varchar (40) not null, );   create table warehouse (warehouseid varchar(10) not null primary key, qtyofstocks int not null, reorderqty int not null, provinceid varchar(10)foreign key references     warehouse_province(provinceid) );  create table retailshop_provice (retailprovinceid varchar(10) not null primary key, province varchar (25) not null, city varchar (25) not null, addresss varchar (40) not null, );  create table retailshop (retailshopid varchar(10) not null primary key, retailprovinceid varchar(10)foreign key references retailshop_provice(retailprovinceid)); 

i need make query list employees work in both retail shop , warehouses along city work. made sql statement

select  e.employeeid,e.fname, e.mname, e.lname,whp.city,rsp.city employee e,warehouse w ,warehouse_province whp,retailshop   r,retailshop_provice rsp  (e.warehouseid=w.warehouseid , w.provinceid = whp.provinceid)or(e.retailshopid=r.retailshopid , r.retailprovinceid = rsp.retailprovinceid); 

however result has 2 rows city,how combine them below sql result want show 1 row city without including in 2 seperate sql want combine 1 sql results showing employee names , city work at.

select  e.employeeid,e.fname, e.mname, e.lname,whp.city employee e,warehouse w ,warehouse_province whp  (e.warehouseid=w.warehouseid , w.provinceid = whp.provinceid)  select e.employeeid, e.fname, e.mname, e.lname,rsp.city employee e,retailshop r,retailshop_provice rsp  e.retailshopid=r.retailshopid  ,  r.retailprovinceid=rsp.retailprovinceid; 

sample table should this

employee  fname lname  whpcity rspcity           mr    x      null  city2  b         mr    y      city1   null          mr    x      null  city1  b         mr    y      city2   null 

i not clear expected output. assume output got below

employee  fname lname  whpcity rspcity           mr    x      city1   city2  b         mr    y      city1   city2          mr    x      city2   city1  b         mr    y      city2   city1 

in above need use stuff along xmlpath concatenate city's belong each employee

select employee, fname, lname, stuff ((select ','+ whpcity                                  #emp e1                                 e1.employee = e2.employee                                  xml path ('')) ,1,1,'') whpcity #emp e2 group employee, fname, lname 

the output below

employee    fname       lname       whpcity           mr          x           city1,city2 b           mr          y           city2,city1 

also suggest use ansi-syntax while joining 2 tables.

select  e.employeeid,e.fname, e.mname, e.lname,whp.city whpcity,rsp.city rspcity employee e inner join  warehouse w  on ( e.warehouseid=w.warehouseid) inner join warehouse_province whp on (w.provinceid = whp.provinceid) inner join retailshop r on (e.retailshopid=r.retailshopid) inner join retailshop_provice rsp on (r.retailprovinceid = rsp.retailprovinceid)