ID | Country
---------------
1 | French, China,Japan
2 | Malaysia, Singapore
Table 2
ID | Country1
---------------
1 | Japan, China
Result i want :
ID | Country
---------------
1 | French, Malaysia, Singapore
create table test1
(id int,
country varchar(50))
create table test2
(id int,
country varchar(50))
insert into test1 values (1,'French, China,Japan')
insert into test1 values (2,'Malaysia, Singapore')
insert into test2 values (1,'Japan, China')
WITH extract1 (country_Attributes) AS
(
SELECT
CONVERT(XML,'
+ REPLACE(country,',', '
+ '
FROM test1
union all
SELECT
CONVERT(XML,'
+ REPLACE(country,',', '
+ '
FROM test2
)
SELECT
country_Attributes.value('/country[1]/Attribute[1]','varchar(25)') AS a,
country_Attributes.value('/country[1]/Attribute[2]','varchar(25)') AS b,
country_Attributes.value('/country[1]/Attribute[3]','varchar(25)') AS c,
country_Attributes.value('/country[1]/Attribute[4]','varchar(25)') AS d
into #temp
FROM extract1;
select country into #temp2 from
(SELECT rtrim(ltrim(colval)) as country
FROM
(SELECT a, b,c,d
FROM #temp) p
UNPIVOT
(ColVal FOR Col IN
(a, b, c,d)
)AS unpvt) tmp
group by country
having count(1)<2
declare @retstr varchar(8000)
select @retstr = COALESCE(@retstr + ',','') + country
from #temp2
print @retstr
drop table #temp
drop table #temp2