Merge a two way relation in the same table in SQL Server -
current data
id | name1 | name2 <guid1> | xmind | mindnode <guid2> | mindnode | xmind <guid3> | avast | hitman pro <guid4> | hitman pro | avast <guid5> | pplive | hola! <guid6> | zenmate | hola! <guid7> | hola! | pplive <guid8> | hola! | zenmate required output
id1 | id2 | name1 | name2 <guid1> | <guid2> | xmind | mindnode <guid3> | <guid4> | avast | hitman pro <guid5> | <guid7> | pplive | hola! <guid6> | <guid8> | hola! | zenmate these relations between apps. want show avast , hitman has relation in view not need show in "direction" have relation. it's given in view relation goes both ways.
edit: seems example simple. solution doesn't work more data.
declare @a table (id int, name1 varchar(50), name2 varchar(50)) insert @a values ( 1, 'xmind', 'mindnode' ) insert @a values ( 2, 'mindnode', 'xmind' ) insert @a values ( 3, 'avast', 'hitman pro' ) insert @a values ( 4, 'hitman pro', 'avast' ) insert @a values ( 5, 'pplive video accelerator', 'hola! better internet' ) insert @a values ( 6, 'zenmate', 'hola! better internet' ) insert @a values ( 7, 'hola! better internet', 'pplive video accelerator' ) insert @a values ( 8, 'hola! better internet', 'zenmate' ) select a1.id id1 , a2.id id2 , a1.name1 , a2.name1 name2 @a a1 join @a a2 on a1.name1 = a2.name2 , a1.id < a2.id -- avoid duplicates this works guess it's guid messing me.
edit again:
i haven't looked @ while , thought worked realized not. i've struggled morning must admit sql not strong suite. thing this.
declare @a table (id int, name1 varchar(50), name2 varchar(50)) insert @a values ( 1, 'xmind', 'mindnode' ) insert @a values ( 2, 'mindnode', 'xmind' ) insert @a values ( 3, 'avast', 'hitman pro' ) insert @a values ( 4, 'pplive video accelerator', 'hola! better internet' ) insert @a values ( 5, 'zenmate', 'hola! better internet' ) insert @a values ( 6, 'hitman pro', 'avast' ) insert @a values ( 7, 'hola! better internet', 'pplive video accelerator' ) insert @a values ( 8, 'hola! better internet', 'zenmate' ) insert @a values ( 9, 'xx', 'a' ) insert @a values ( 10, 'xx', 'bb' ) insert @a values ( 11, 'bb', 'xx' ) insert @a values ( 12, 'a', 'xx' ) insert @a values ( 13, 'xx', 'cc' ) insert @a values ( 14, 'cc', 'xx' ) ;with cte ( select a1.id id1 , a2.id id2 , a1.name1 , a2.name1 name2, checksum(case when a1.name1>a2.name1 a2.name1+a1.name1 else a1.name1+a2.name1 end) ck, -- display row_number() on (partition checksum(case when a1.name1>a2.name1 a2.name1+a1.name1 else a1.name1+a2.name1 end) order checksum(case when a1.name1>a2.name1 a2.name1+a1.name1 else a1.name1+a2.name1 end)) rn @a a1 join @a a2 on a1.name1 = a2.name2 ) select id1, id2,name1, name2 cte c1 rn=1 when use code sure works fine names doesn't match id's correctly.
the result is
id1 | id2 | name1 | name2 12 | 9 | | x (correct) 7 | 5 | hola! | zenmate (not correct) [..] i've pulled hair morning can't figure out. still use guid's id's , use int's here make bit more readable.
if output should contain two-way relations ('xx' + 'a') , ('a' + 'xx'), try this:
; m (id1, id2, name1, name2) ( select id1, id2, name1, name2 ( select a1.id id1 ,a2.id id2 ,a1.name1 name1 ,a2.name1 name2 ,row_number() on (partition a1.name1, a2.name1 order (select 1)) n @a a1 join @a a2 on a1.name1 = a2.name2 , a1.name2 = a2.name1 ) t n = 1 ) select distinct * ( select id1, id2, name1, name2 m id1 <= id2 union select id2, id1, name2, name1 m id1 > id2 ) dm it produces output follows:
+------+-----+--------------------------+-----------------------+ | id1 | id2 | name1 | name2 | +------+-----+--------------------------+-----------------------+ | 1 | 2 | xmind | mindnode | | 3 | 6 | avast | hitman pro | | 4 | 7 | pplive video accelerator | hola! better internet | | 5 | 8 | zenmate | hola! better internet | | 9 | 12 | xx | | | 10 | 11 | xx | bb | | 13 | 14 | xx | cc | +------+-----+--------------------------+-----------------------+
Comments
Post a Comment