Find Combinations of Values in a Single Column - Postgresql

Question

I have a Input Table of this structure

Input :

Col1 | Col2 | Col3

O1 | P1 | 100
O1 | P2 | 200
O1 | P3 | 300
O2 | P1 | 100
O2 | P2 | 200

I need to find the Different Combinations and aggregate the Values in another Column at the same time.

Output Expected :

Col1 | Col2 | COl3

O1 | P1,P2 | 300

O1 | P2,P3 | 500

O1 | P3,P1 | 400

O1 | P1,P2,P3 |600

O2 | P1,P2 | 300

Need some help in how to approach this Problem and Where to start.

Man, I had your answer and then you completely changed the question. :( For the previous version, string_agg would have been a simple solution. Have a look at postgresql.org/docs/devel/static/functions-aggregate.html — opatut
– opatut, Commented Apr 5, 2017 at 5:50
Haha.. It's not class homework LOL. I have Recreated one of the Business Problems in sales Here. — Sowmiya Raja Radhakrishnan
– Sowmiya Raja Radhakrishnan, Commented Apr 5, 2017 at 6:21
Is there a maximum number of different values (es. P4, P5, ... ) for same COL1? — etsa
– etsa, Commented Apr 5, 2017 at 6:52

etsa · Accepted Answer · 2017-04-05 07:47:40Z

1

You can try this (where X is your table):

WITH RECURSIVE R  AS (SELECT X.COL1, X.COL2, CAST(X.COL2 AS VARCHAR(100)) AS COMBI, COL3 AS TOT
           FROM X
           UNION ALL
           SELECT X.COL1, X.COL2,  CAST( r.COMBI || CAST(',' AS VARCHAR(1)) || X.COL2  AS VARCHAR(100)), X.COL3+R.TOT AS TOT
            FROM R 
            INNER JOIN X ON X.COL2 > R.COL2 AND X.COL1 = R.COL1)
SELECT COL1, COMBI, TOT 
FROM R
WHERE LENGTH(COMBI) >LENGTH(COL2)
ORDER BY COL1, LENGTH(COMBI), COMBI
;

Output (with data as you posted):

    col1    combi   tot
1   O1  P1,P2   300
2   O1  P1,P3   400
3   O1  P2,P3   500
4   O1  P1,P2,P3    600
5   O2  P1,P2   300

Output (adding another row P4 in O1):

    col1    combi   tot
1   O1  P1,P2   300
2   O1  P1,P3   400
3   O1  P1,P4   500
4   O1  P2,P3   500
5   O1  P2,P4   600
6   O1  P3,P4   700
7   O1  P1,P2,P3    600
8   O1  P1,P2,P4    700
9   O1  P1,P3,P4    800
10  O1  P2,P3,P4    900
11  O1  P1,P2,P3,P4 1000
12  O2  P1,P2   300

edited Apr 5, 2017 at 7:47

answered Apr 5, 2017 at 7:13

etsa

5,0501 gold badge9 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sowmiya Raja Radhakrishnan Over a year ago

Thanks, Etsa. You saved me hours of Work.

A l w a y s S u n n y Over a year ago

@esta, can you please have a look on it stackoverflow.com/questions/66794991/…

Collectives™ on Stack Overflow

Find Combinations of Values in a Single Column - Postgresql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related