Jake Jake - 9 months ago 25
SQL Question

SQL: swap two to-from (directional) columns into two unidirectional columns

I am dealing with some graph related data, which has directional to-from nodes. This is the initial mock data.

enter image description here

However, I am trying to analyse it as unidirectional path. This means that I will somehow need to swap some of the rows of node1 with node2 to do a group by. Like below.

enter image description here

Does anyone know how to write an SQL query for this? Thanks!

heres the mock initial data in comma delimited.

id,node1,node2,count
1,A,B,10
2,B,A,20
3,C,B,30
4,C,A,30
5,A,C,20
6,B,C,10

Answer Source

You apparently want the smaller value to be node1. This can be done using the least() and greatest() functions:

select id, 
       least(node1, node2) as node1, 
       greatest(node1, node2) as node2,
       "count"
from the_table;