How to rewrite this query (PostgreSQL) in SQL Server? -

few days ago have asked question 1,2 , 3. degree connections. question link , @snoopy gave article link can fix problems. article link

i have examined article unable use recursive query sql server.

postgresql query:

select you,    b mightknow,    shared_connection,    case      when (n1.feat1 = n2.feat1 , n1.feat1 = n3.feat1) 'feat1 in common'      when (n1.feat2 = n2.feat2 , n1.feat2 = n3.feat2) 'feat2 in common'      else 'nothing in common'    end reason  (  recursive transitive_closure(a, b, distance, path_string)  ( select a, b, 1 distance,      || '.' || b || '.' path_string,      b direct_connection edges2  = 1 -- set starting node   union   select tc.a, e.b, tc.distance + 1,      tc.path_string || e.b || '.' path_string,      tc.direct_connection edges2 e join transitive_closure tc on e.a = tc.b  tc.path_string not '%' || e.b || '.%'  , tc.distance < 2  )  select a,    b,    direct_connection shared_connection  transitive_closure  distance = 2  ) youmightknow  left join nodes n1 on youmightknow.a = n1.id  left join nodes n2 on youmightknow.b = n2.id  left join nodes n3 on youmightknow.shared_connection = n3.id  (n1.feat1 = n2.feat1 , n1.feat1 = n3.feat1)  or (n1.feat2 = n2.feat2 , n1.feat2 = n3.feat2); 

or just

with recursive transitive_closure(a, b, distance, path_string) ( select a, b, 1 distance,      || '.' || b || '.' path_string edges = 1 -- source  union  select tc.a, e.b, tc.distance + 1,      tc.path_string || e.b || '.' path_string edges e join transitive_closure tc on e.a = tc.b tc.path_string not '%' || e.b || '.%' ) select * transitive_closure b=6 -- destination order a, b, distance; 

as said, don't know how write recursive query sql server using ctes. made search , examined this page still no luck. couldn't run query.

if interested, here answer;

i managed convert query in question sql by;

1. converting integer values varchar(max). if don't specify length of varchar max, you'll "types don't match between anchor , recursive part in column..."

2. i replaced || +

3. i added ; beginning of query

4. finally @a_horse_with_no_name proposed removed recursive query.

result;

;with transitive_closure(a, b, distance, path_string) ( select a, b, 1 distance,  cast(a varchar(max)) + '.' + cast(b varchar(max)) + '.' path_string edges = 1 -- source  union  select tc.a, e.b, tc.distance + 1,  cast(tc.path_string varchar(max)) + cast(e.b varchar(max)) + '.' path_string edges e join transitive_closure tc on e.a = tc.b tc.path_string not '%' + cast(e.b varchar(max)) + '.%' ) select * transitive_closure b=6 -- destination order a, b, distance;