Friday, September 11, 2009

Compare delimiter data from 2 table

Table 1
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,',', '
')
+ '
') AS country_Attributes
FROM test1
union all
SELECT
CONVERT(XML,''
+ REPLACE(country,',', '
')
+ '
') AS country_Attributes
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