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

Question

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 || '.' || 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.

score 4 · Accepted Answer · answered May 01 '11 at 12:31

If someone interested, here is the answer;

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

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..."
I replaced || to +
I added ; to the beginning of query
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;

score 2 · Answer 2 · answered Apr 29 '11 at 16:56

2

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 +

answered Apr 29 '11 at 16:56

I removed recursive and it works but I couldn't run this query. `SELECT edges.a as a, edges.b as b, 1 AS distance, a + '.' + b + '.' as path FROM edges` Error: Conversion failed when converting the varchar value '.' to data type int I tried to use CAST but no success. – Burak F. Kilicaslan May 01 '11 at 11:48
@Burak: apparently `a` and `b` are not varchar columns. You will need to cast them in order to be able to concatenate them. Try `cast(edges.a as varchar) + '.' + cast(edges.b as varchar)` – May 01 '11 at 11:55
I managed to run this query : `SELECT edges.a as a, edges.b as b, 1 AS distance, CAST(a AS VARCHAR(10)) + '.' + CAST(b AS VARCHAR(10)) + '.' as path FROM edges` – Burak F. Kilicaslan May 01 '11 at 11:56

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

2 Answers2