INSERT INTO VALUES ( NewID(), 'Hola! Better Internet', 'PPLive Video Accelerator' ) INSERT INTO VALUES ( NewID(), 'Hola! Better Internet', 'ZenMate' ) INSERT INTO VALUES ( NewID(), 'XX', 'A' ) INSERT INTO VALUES ( NewID(), 'A', 'XX' ) INSERT INTO VALUES ( NewID(), 'XX', 'BB' ) INSERT INTO VALUES ( NewID(), 'BB', 'XX' ) INSERT INTO VALUES ( NewID(), 'XX', 'CC' ) INSERT INTO VALUES ( NewID(), 'CC', 'XX' ) With CTE as ( SELECT a1.ID AS ID1 , INSERT INTO VALUES ( NewID(), 'ZenMate', 'Hola! Better Internet' )
INSERT INTO VALUES ( NewID(), 'avast', 'Hitman Pro' ) INSERT INTO VALUES ( NewID(), 'Hitman Pro', 'avast' ) INSERT INTO VALUES ( NewID(), 'PPLive Video Accelerator', 'Hola! Better Internet' ) INSERT INTO VALUES ( NewID(), 'MindNode', 'XMind' ) Row_Number() over (Partition by CheckSum(Case when a1.Name1>a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end) order by CheckSum(Case when a1.Name1>a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end)) as rn FROM a1 JOIN a2 ON a1.Name1 = a2.Name2 ) Select ID1, ID2,Name1, Name2 from CTE C1 where rn=1ĭECLARE TABLE (ID uniqueIdentifier, Name1 VARCHAR(50), Name2 VARCHAR(50)) INSERT INTO VALUES ( NewID(), 'XMind', 'MindNode' ) INSERT INTO VALUES ( 7, 'Hola! Better Internet', 'PPLive Video Accelerator' ) INSERT INTO VALUES ( 8, 'Hola! Better Internet', 'ZenMate' ) INSERT INTO VALUES ( 9, 'XX', 'A' ) INSERT INTO VALUES ( 10, 'XX', 'BB' ) INSERT INTO VALUES ( 11, 'BB', 'XX' ) INSERT INTO VALUES ( 12, 'A', 'XX' ) INSERT INTO VALUES ( 13, 'XX', 'CC' ) INSERT INTO VALUES ( 14, 'CC', 'XX' ) With CTE as ( SELECT a1.ID AS ID1 ,ĬheckSum(Case when a1.Name1>a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end) ck, - just for display INSERT INTO VALUES ( 5, 'ZenMate', 'Hola! Better Internet' ) INSERT INTO VALUES ( 6, 'Hitman Pro', 'avast' ) INSERT INTO VALUES ( 3, 'avast', 'Hitman Pro' ) INSERT INTO VALUES ( 4, 'PPLive Video Accelerator', 'Hola! Better Internet' ) I still use Guid's as ID's and just use Int's here to make it a bit more readable.ĭECLARE TABLE (ID int, Name1 VARCHAR(50), Name2 VARCHAR(50)) INSERT INTO VALUES ( 1, 'XMind', 'MindNode' ) I've pulled my hair all morning but i can't figure this out.
:max_bytes(150000):strip_icc()/MindNode-a38c668f8061466e8c2891f0989d3e20.jpg "mindnode merge 2 maps")
MINDNODE MERGE 2 MAPS CODE
When i use this code it sure works fine with the names but it doesn't match the ID's correctly. Order by CheckSum(Case when a1.Name1>a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end)) as rn Row_Number() over (Partition by CheckSum(Case when a1.Name1>a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end) INSERT INTO VALUES ( 8, 'Hola! Better Internet', 'ZenMate' )ĪND a1.ID a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end) ck, - just for display INSERT INTO VALUES ( 7, 'Hola! Better Internet', 'PPLive Video Accelerator' ) INSERT INTO VALUES ( 6, 'ZenMate', 'Hola! Better Internet' )
INSERT INTO VALUES ( 5, 'PPLive Video Accelerator', 'Hola! Better Internet' )
INSERT INTO VALUES ( 4, 'Hitman Pro', 'avast' ) INSERT INTO VALUES ( 3, 'avast', 'Hitman Pro' ) INSERT INTO VALUES ( 2, 'MindNode', 'XMind' ) INSERT INTO VALUES ( 1, 'XMind', 'MindNode' ) The solution doesn't work with more data.ĭECLARE TABLE (ID INT, Name1 VARCHAR(50), Name2 VARCHAR(50)) It's a given in this view that the relation goes both ways.ĮDIT: Seems like my example was to simple. I want to show that Avast and Hitman has a relation but in this view i do not need to show in what "direction" they have an relation.
0 kommentar(er)
