HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

DISTINCT column combination with permutations

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
combinationdistinctcolumnwithpermutations

Problem

My postgresql column structure looks like this:

id  | from | to
---------------
1   |  A   |  B
2   |  A   |  B 
3   |  C   |  D


Now I want an result which looks like this:

res 
-----
'A:B'
'B:A' 
'C:D'
'D:C'


where the first and the rows are permuted from A:B to B:A and 'C:D' to 'D:C' and the second column is omitted due to distinct operation.

Solution

This looks more like a stack overflow question yet Union will give a distinct combination.

SELECT concat("from",':',"to") as res From Table
UNION
SELECT concat("to",':',"from") From Table

Code Snippets

SELECT concat("from",':',"to") as res From Table
UNION
SELECT concat("to",':',"from") From Table

Context

StackExchange Database Administrators Q#233642, answer score: 6

Revisions (0)

No revisions yet.