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

Few days ago I have asked a question about 1,2 and 3. degree connections. Question Link and @Snoopy gave an article link which can fix all my problems. Article Link

I have carefully examined this article but I was unable to use With Recursive query with SQL Server.

PostgreSQL Query:

SELECT a AS you,
   b AS mightknow,
   shared_connection,
   CASE
     WHEN (n1.feat1 = n2.feat1 AND n1.feat1 = n3.feat1) THEN 'feat1 in common'
     WHEN (n1.feat2 = n2.feat2 AND n1.feat2 = n3.feat2) THEN 'feat2 in common'
     ELSE 'nothing in common'
   END AS reason
 FROM (
 WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS
 ( SELECT a, b, 1 AS distance,
     a || '.' || b || '.' AS path_string,
     b AS direct_connection
FROM edges2
 WHERE a = 1 -- set the starting node

 UNION ALL

 SELECT tc.a, e.b, tc.distance + 1,
     tc.path_string || e.b || '.' AS path_string,
     tc.direct_connection
FROM edges2 AS e
JOIN transitive_closure AS tc ON e.a = tc.b
 WHERE tc.path_string NOT LIKE '%' || e.b || '.%'
 AND tc.distance < 2
 )
 SELECT a,
   b,
   direct_connection AS shared_connection
 FROM transitive_closure
 WHERE distance = 2
 ) AS youmightknow
 LEFT JOIN nodes AS n1 ON youmightknow.a = n1.id
 LEFT JOIN nodes AS n2 ON youmightknow.b = n2.id
 LEFT JOIN nodes AS n3 ON youmightknow.shared_connection = n3.id
 WHERE (n1.feat1 = n2.feat1 AND n1.feat1 = n3.feat1)
 OR (n1.feat2 = n2.feat2 AND n1.feat2 = n3.feat2);

or just

WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS
( SELECT a, b, 1 AS distance,
     a || 开发者_StackOverflow中文版'.' || b || '.' AS path_string
FROM edges
WHERE a = 1 -- source

UNION ALL

SELECT tc.a, e.b, tc.distance + 1,
     tc.path_string || e.b || '.' AS path_string
FROM edges AS e
JOIN transitive_closure AS tc ON e.a = tc.b
WHERE tc.path_string NOT LIKE '%' || e.b || '.%'
)
SELECT * FROM transitive_closure
WHERE b=6 -- destination
ORDER BY a, b, distance;

As I said, I don't know how to write a recursive query with SQL Server using CTEs. Made a search and examined this page but still no luck. I couldn't run the query.

If someone interested, here is the answer;

I managed to convert the query in question to SQL by;

1. converting integer values to varchar(MAX). If you don't specify the length of varchar as MAX, you'll get "Types don't match between the anchor and the recursive part in column..."

2. I replaced || to +

3. I added ; to the beginning of query

4. Finally as @a_horse_with_no_name proposed I removed RECURSIVE from query.

Result;

;WITH transitive_closure(a, b, distance, path_string) AS
( SELECT a, b, 1 AS distance,
 CAST(a as varchar(MAX)) + '.' + CAST(b as varchar(MAX)) + '.' AS path_string
FROM edges
WHERE a = 1 -- source

UNION ALL

SELECT tc.a, e.b, tc.distance + 1, 
CAST(tc.path_string as varchar(MAX)) + CAST(e.b as varchar(MAX)) + '.' AS path_string
FROM edges AS e
JOIN transitive_closure AS tc ON e.a = tc.b
WHERE tc.path_string NOT LIKE '%' + CAST(e.b as varchar(MAX)) + '.%'
)
SELECT * FROM transitive_closure
WHERE b=6 -- destination
ORDER BY a, b, distance;

The recursive CTE should be the same on SQL Server (at least on a recent version, this was introduced with SQL Server 2005 if I'm not mistaken), just leave out the recursive keyword.

Note that SQL Server does not comply with the SQL standard and therefor you need to replace the || concatenation with +