CODEWITHSUNDEEP
sqlpostgresqlgraph-theory
Jake
Jake

Reputation: 2923

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

Upvotes: 0

Views: 278

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 1

Related Questions