MS SQL query that will select all linked node pairs for D3 sankey diagram

Question

I've got following [AllPathsTbl] and I'm trying to write a query that when I pass a node name it will select rows that are linked to that node (also indirectly i.e. through other links)

SELECT * FROM [AllPathsTbl]

Node1   Node2   TotalValue
D1  Q1  4693094
D2  Q2  1144506
D3  Q3  881685
D4  Q4  2232370
D5  Q5  861860
D6  Q6  27750
D7  Q7  119430
D8  Q8  26200
D9  Q9  2054170
E1  P1  17275
E2  P1  36675
E3  P2  323400
E3  P1  242910
E4  P3  133070
E4  P4  834600
E4  P1  375800
E4  P5  888900
E5  P6  186263
E5  P7  33609
E6  P6  464289
E7  P6  1343779
I13 R15 323400
I16 R15 1427130
I17 R16 1399071
P2  I13 323400
P3  I16 133070
P4  I16 834600
P6  I17 1399071
P1  I16 672660
Q5  E3  861860
Q4  E4  2232370
Q3  E5  881685
Q2  E8  100
Q2  E6  1144506
Q1  E7  4693094
Q6  E1  17275
Q6  E2  10475
Q8  E2  26200
Q7  E9  119430
Q9  E8  370070

Expected output for nodes for example P5 or E4 (or basically any from the below table) would always be like that:

SELECT * FROM [dbo].[FilteredPathsTbl]('E4')

Node1   Node2   TotalValue
D4  Q4  2232370
D5  Q5  861860
D6  Q6  27750
D8  Q8  26200
E1  P1  17275
E2  P1  36675
E3  P2  323400
E3  P1  242910
E4  P3  133070
E4  P4  834600
E4  P1  375800
E4  P5  888900
I13 R15 323400
I16 R15 1427130
P2  I13 323400
P3  I16 133070
P4  I16 834600
P1  I16 672660
Q5  E3  861860
Q4  E4  2232370
Q6  E1  17275
Q6  E2  10475
Q8  E2  26200

Graphical representation:

Help needed as I have no more hairs to pull ;)

there should be SELECT * FROM [dbo].FilteredPathsTbl('E4') not SELECT * FROM [dbo].FilteredPathsTbl — Pawel, Aug 25 '17 at 12:04
Hi Veljko89. I created SQLFiddle with connections between nodes: http://sqlfiddle.com/#!6/78d07a/41 there are 40 links and the aim is to return ony 23 as per "Graphical representation" when for example 'P4' is queried — Pawel, Aug 27 '17 at 11:13

ttallierchio · Answer 1 · 2017-08-25T14:52:39.123

What you want is a recursive CTE for this, below is a working query for the data you provided. after some quick checking, it appears to work and provide the links going back and forth for you, obviously if you only want one direction use only one of the CTEs

DECLARE @startnode VARCHAR(10) = 'P4';

WITH path_nodes_cte_left
AS (
    SELECT node1,
        node2,
        t.total_value,
        0 LEVEL
    FROM allpathstbl t
    WHERE node2 = @startnode

    UNION ALL

    SELECT to_left.node1,
        to_left.node2,
        to_left.total_value,
        LEVEL - 1 LEVEL
    FROM allpathstbl to_left
    INNER JOIN path_nodes_cte_left prev ON to_left.node2 = prev.node1
    ),
path_nodes_cte_right
AS (
    SELECT node1,
        node2,
        t.total_value,
        0 LEVEL
    FROM allpathstbl t
    WHERE node1 = @startnode

    UNION ALL

    SELECT to_right.node1,
        to_right.node2,
        to_right.total_value,
        LEVEL + 1 LEVEL
    FROM allpathstbl to_right
    INNER JOIN path_nodes_cte_right next ON to_right.node1 = next.node2
    )
SELECT node1
FROM path_nodes_cte_left
WHERE node1 != @startnode

UNION

SELECT node1
FROM path_nodes_cte_right
WHERE node1 != @startnode
ORDER BY LEVEL

Hi @ttallierchio and than you for your response. I tried your solution in SQLFiddle as I don't have access to work environment right now.It looks like it returns links from only one depth, that is for node 'P4' it returns 5 links: D4-Q4, Q4-E4, E4-P4, P4-I16, I16-R15 whilst the aim is to return all 23 links as in the graphics. Also result schema should be the same as source table, but I was able to amended your code http://sqlfiddle.com/#!6/78d07a/40 — Pawel, Aug 27 '17 at 11:00

MS SQL query that will select all linked node pairs for D3 sankey diagram

1 Answers1