DROP TABLE IF EXISTS Person;
GO
CREATE TABLE Person (
  ID INT IDENTITY PRIMARY KEY,
  Name VARCHAR(50) NOT NULL
) AS NODE;
INSERT INTO Person (Name) VALUES
('Mary'),
('Bob'),
('John'),
('Francis'),
('Serg')

DROP TABLE IF EXISTS Friendof;
GO
CREATE TABLE Friendof AS EDGE;
INSERT INTO Friendof ($from_id, $to_id) VALUES (
  (SELECT $node_id FROM Person WHERE ID = 1), 
  (SELECT $node_id FROM Person WHERE ID = 2));
INSERT INTO Friendof ($from_id, $to_id) VALUES (
  (SELECT $node_id FROM Person WHERE ID = 2), 
  (SELECT $node_id FROM Person WHERE ID = 3));
INSERT INTO Friendof ($from_id, $to_id) VALUES (
  (SELECT $node_id FROM Person WHERE ID = 5), 
  (SELECT $node_id FROM Person WHERE ID = 4));
INSERT INTO Friendof ($from_id, $to_id) VALUES (
  (SELECT $node_id FROM Person WHERE ID = 4), 
  (SELECT $node_id FROM Person WHERE ID = 2));

select p1.name, p2.name Friend
from Person p1, friendOf f, Person p2
where match (p1-(f)->p2)


SELECT PersonName+ '->'+ Friends Friend
FROM (	
	SELECT
		Person1.name AS PersonName, 
		STRING_AGG(Person2.name, '->') WITHIN GROUP (GRAPH PATH) AS Friends,
		LAST_VALUE(Person2.name) WITHIN GROUP (GRAPH PATH) AS LastNode
	FROM
		Person AS Person1,
		friendOf FOR PATH AS fo,
		Person FOR PATH  AS Person2
	WHERE MATCH(SHORTEST_PATH(Person1(-(fo)->Person2)+))
	AND Person1.name = 'Serg'
) AS Q
WHERE Q.LastNode = 'Bob'

;WITH frd AS
(
  SELECT $node_id NodeID, name , 
    CAST(NULL AS VARCHAR(50)) Friend
  FROM person
  WHERE name = 'Bob'
  UNION ALL
  SELECT fe.$node_id, fe.name , frd.name Friend
  FROM person fe INNER JOIN friendOf rt
      ON fe.$node_id = rt.$from_id 
   INNER JOIN frd
      ON rt.$to_id = frd.NodeID
)

SELECT name, Friend FROM frd
WHERE Friend IS NOT NULL

;WITH frd1 AS
(
  SELECT $node_id NodeID, name, 
    CAST('N/A' AS VARCHAR(50)) Friend, 1 AS Tier
  FROM person
  WHERE name = 'Bob'
  UNION ALL
  SELECT fe.$node_id, fe.name, frd1.name Friend ,
    (Tier + 1) AS Tier
  FROM person fe INNER JOIN friendOf rt
      ON fe.$node_id = rt.$from_id 
   INNER JOIN frd1
      ON rt.$to_id = frd1.NodeID
)
SELECT name, Tier, Friend 
FROM frd1 
ORDER BY Tier, Friend, name;