SQL Problem - Query to connect movement transactions (FROM-TO) in a SQL table without looping

Question

I have a very big table which contains product movement transactions (FROM-TO).

Example Table

+-----------------+----------------+--------------+--------+
| FROM STATION ID | TO STATION ID  | Product Type | Volume |
+-----------------+----------------+--------------+--------+
| A               | B              | T1           |   1000 |
| B               | C              | T1           |    300 |
| B               | D              | T1           |    400 |
| C               | E              | T1           |    200 |
| C               | F              | T1           |    100 |
+-----------------+----------------+--------------+--------+

I need to connect these transaction together in SQL way so that I could allocate cost for those volume to each station properly.

Expected Result

1. A->B->C->E
2. A->B->C->F
3. A->B->D

Currently, I'm doing this by looping to join the table with the table itself to connect transactions. In this case, it would loop for joining the original table itself for 3 times. However, it takes way too much time to process, especially when number of transactions is increased.

Could you help advise me any smarter SQL way to solve this problem?

Thanks.

Answer 1

Try this can replace group_concat with listagg depending upon the database

     Select [from] , group_concat(case 
     when [from] 
      <> t.[from] 
        and
        to=t.[from] then t.to end, ',' ) 
         from(  Select 
       * FROM  table) t group by
         from