Skip to Main Content
  • Questions

  • SQL questions involving combinations

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Dan.

Asked: September 20, 2002 - 12:05 pm UTC

Last updated: March 02, 2020 - 2:58 pm UTC

Version: 8.1.7.1

Viewed 10K+ times! This question is

You Asked

Dear Tom...

I have two questions that are somewhat related to each other in concept:

1) Consider the following table which represents a state history per each person identified by PERSON_ID:

SQL> CREATE TABLE status_history
2 (person_id NUMBER,
3 status_code VARCHAR2(1),
4 status_date DATE)
5 /

Table created.

SQL> INSERT INTO status_history
2 SELECT 1, 'A', TO_DATE('01-JAN-2002', 'DD-MON-YYYY') FROM DUAL
3 UNION SELECT 1, 'B', TO_DATE('02-JAN-2002', 'DD-MON-YYYY') FROM DUAL
4 UNION SELECT 1, 'C', TO_DATE('03-JAN-2002', 'DD-MON-YYYY') FROM DUAL
5 UNION SELECT 2, 'A', TO_DATE('03-JAN-2002', 'DD-MON-YYYY') FROM DUAL
6 UNION SELECT 2, 'C', TO_DATE('05-JAN-2002', 'DD-MON-YYYY') FROM DUAL
7 UNION SELECT 2, 'B', TO_DATE('06-JAN-2002', 'DD-MON-YYYY') FROM DUAL
8 UNION SELECT 3, 'B', TO_DATE('02-JAN-2002', 'DD-MON-YYYY') FROM DUAL
9 UNION SELECT 3, 'A', TO_DATE('07-JAN-2002', 'DD-MON-YYYY') FROM DUAL
10 UNION SELECT 3, 'C', TO_DATE('08-JAN-2002', 'DD-MON-YYYY') FROM DUAL
11 UNION SELECT 4, 'B', TO_DATE('01-JAN-2002', 'DD-MON-YYYY') FROM DUAL
12 UNION SELECT 4, 'A', TO_DATE('05-JAN-2002', 'DD-MON-YYYY') FROM DUAL
13 UNION SELECT 4, 'C', TO_DATE('08-JAN-2002', 'DD-MON-YYYY') FROM DUAL
13 /

12 rows created.

SQL> SELECT * FROM status_history
2 ORDER BY person_id, status_date
3 /

PERSON_ID S STATUS_DA
--------- - ---------
1 A 01-JAN-02
1 B 02-JAN-02
1 C 03-JAN-02
2 A 03-JAN-02
2 C 05-JAN-02
2 B 06-JAN-02
3 B 02-JAN-02
3 A 07-JAN-02
3 C 08-JAN-02
4 B 01-JAN-02
4 A 05-JAN-02
4 C 08-JAN-02

12 rows selected.

My question is: how does one determine all possible sets of state changes? In other words, the desirable result is something like the following:

COMBO STATUS_CODE
----- -----------
1 A
1 B
1 C

2 A
2 C
2 B

3 B
3 A
3 C

2) Consider the following table which holds attributes for things:

SQL> CREATE TABLE thing_attributes
2 (thing_id NUMBER,
3 attribute_code VARCHAR2(1))
4 /

Table created.

SQL> INSERT INTO thing_attributes
2 SELECT 1, 'A' FROM DUAL
3 UNION SELECT 1, 'B' FROM DUAL
4 UNION SELECT 2, 'B' FROM DUAL
5 UNION SELECT 2, 'C' FROM DUAL
6 UNION SELECT 2, 'D' FROM DUAL
7 UNION SELECT 3, 'A' FROM DUAL
8 UNION SELECT 3, 'D' FROM DUAL
9 UNION SELECT 4, 'D' FROM DUAL
10 /

8 rows created.

SQL> SELECT * FROM thing_attributes
2 /

THING_ID A
--------- -
1 A
1 B
2 B
2 C
2 D
3 A
3 D
4 D

8 rows selected.

Each thing can have at least one and no more than four of the attributes 'A', 'B', 'C', or 'D'.

Now consider the following table which contain things belonging to persons:

SQL> CREATE TABLE belongings
2 (person_id NUMBER,
3 thing_id NUMBER)
4 /

Table created.

SQL> INSERT INTO belongings
2 SELECT 100, 1 FROM DUAL
3 UNION SELECT 100, 2 FROM DUAL
4 UNION SELECT 100, 3 FROM DUAL
5 UNION SELECT 100, 4 FROM DUAL
6 UNION SELECT 200, 1 FROM DUAL
7 UNION SELECT 200, 2 FROM DUAL
8 UNION SELECT 200, 4 FROM DUAL
9 /

7 rows created.

SQL> SELECT * FROM belongings
2 /

PERSON_ID THING_ID
--------- ---------
100 1
100 2
100 3
100 4
200 1
200 2
200 4

7 rows selected.

Now the question is: how does one determine all the possible combinations of attributes that a person's collection of "things" can have, choosing only one at a time? In other words, for person 200, the ideal answer would be something like this:

COMBO THING_ID ATTRIBUTE_CODE
----- -------- --------------
1 1 A
1 2 B
1 4 D

2 1 B
2 2 B
2 4 D

3 1 A
3 2 C
3 4 D

4 1 B
4 2 C
4 4 D

5 1 A
5 2 D
5 4 D

6 1 B
6 2 D
6 4 D

To clarify, since there is only one attribute for thing 4, and there are three for thing 2 and two for thing 1, the total number of combinations should be 6.


Can either of these thing be accomplished using pure Oracle SQL, or does this sort of thing require PL/SQL or Java?

Many thanks in advance,

dan kefford

and Tom said...

1) analytics can be VERY useful here:

ops$tkyte@ORA920.US.ORACLE.COM> select distinct rtrim(state_change,'-')
2 from (
3 select person_id,
4 max(decode(rn,1,status_code,null)) ||'-'||
5 max(decode(rn,2,status_code,null)) ||'-'||
6 max(decode(rn,3,status_code,null)) ||'-'||
7 max(decode(rn,4,status_code,null)) ||'-'||
8 max(decode(rn,5,status_code,null)) ||'-'||
9 max(decode(rn,6,status_code,null)) ||'-'||
10 max(decode(rn,7,status_code,null)) ||'-'||
11 max(decode(rn,8,status_code,null)) state_change
12 from ( select person_id, status_code,
13 row_number() over ( partition by person_id
14 order by status_date ) rn
15 from status_history
16 )
17 group by person_id
18 )
19 /

RTRIM(STATE_CHA
---------------
A-B-C
A-C-B
B-A-C

ops$tkyte@ORA920.US.ORACLE.COM>


for example (doesn't make sense to pull along person_id, we could -- but it isn't meaningful at all)

2) that will, i believe (it hurts my head to think of doing this in SQL), require a little bit of procedural processing.

this'll be one of those rare cases where you want to say "forget SQL for a minute, lets just program it"

Rating

  (30 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

excellent

A reader, September 22, 2002 - 3:59 am UTC

excellent codes.
possible problem is: if times of status change is over 8, you have to modify this sql.

Tom Kyte
September 22, 2002 - 10:02 am UTC

Yes, i neglected to point that out -- the number of status changes must be finite and known.

Always something usefull

Chuck Jolley, September 22, 2002 - 12:20 pm UTC

row_number() !?

I guess we now have a full set:
row_number()
dense_rank()
and rank()

Very helpfull!
Thanks,
Chuck


analytics make my head hurt :(

pete, February 19, 2004 - 6:53 am UTC

Tom,

Hope that you can help with this - it is sort of similar to the first post, but I am having real difficulty trying to ammend it for my requirements!

I have a table like this:
create table mgroup (id number, mod varchar2(8), value number);

with dome data like this:
insert into mgroup values (1, 'A', 15);
insert into mgroup values (1, 'B', 15);
insert into mgroup values (1, 'C', 15);
insert into mgroup values (1, 'D', 15);

What I need to do is get a listing of all the possible combinations of "mod" within a particular id which have a combined sum of "value" equal to a given number. (note: the value column could vary and would not always be the same for each row within an id)

eg for the examples above, if the number was 15 then the results would be:
A
B
C
D

if the number was 45 the results would be:
ABC
ABD
ACD
BCD

if the number was 60 then the results would be:
ABCD

thanks,
Pete

Tom Kyte
February 19, 2004 - 11:11 am UTC

don't think you can do this in sql (sort of like their 2cnd part of the question).

well, you can -- but boy would it be the most expensive query in the world (involves this cartesian semi-join thing to get every combo).

How about this...

Padders, February 19, 2004 - 12:26 pm UTC 

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> CREATE TABLE mgroup (
  2    id NUMBER, mod VARCHAR2 (8), value NUMBER);

Table created.

SQL> INSERT INTO mgroup VALUES (1, 'A', 15);

1 row created.

SQL> INSERT INTO mgroup VALUES (1, 'B', 15);

1 row created.

SQL> INSERT INTO mgroup VALUES (1, 'C', 15);

1 row created.

SQL> INSERT INTO mgroup VALUES (1, 'D', 15);

1 row created.

SQL> COLUMN id FORMAT 99
SQL> COLUMN combo FORMAT A5
SQL> SELECT   a.id, REPLACE (a.combo, '#') combo
  2  FROM    (SELECT id, SYS_CONNECT_BY_PATH (mod, '#') || '#' combo
  3           FROM   mgroup
  4           CONNECT BY PRIOR id = id AND mod > PRIOR mod) a, mgroup b
  5  WHERE    b.id = a.id AND INSTR (a.combo, '#' || b.mod || '#') > 0
  6  GROUP BY a.id, a.combo
  7  HAVING   SUM (b.value) = 15;

 ID COMBO
--- -----
  1 A
  1 B
  1 C
  1 D

SQL> SELECT   a.id, REPLACE (a.combo, '#') combo
  2  FROM    (SELECT id, SYS_CONNECT_BY_PATH (mod, '#') || '#' combo
  3           FROM   mgroup
  4           CONNECT BY PRIOR id = id AND mod > PRIOR mod) a, mgroup b
  5  WHERE    b.id = a.id AND INSTR (a.combo, '#' || b.mod || '#') > 0
  6  GROUP BY a.id, a.combo
  7  HAVING   SUM (b.value) = 30;

 ID COMBO
--- -----
  1 AB
  1 AC
  1 AD
  1 BC
  1 BD
  1 CD

6 rows selected.

SQL> SELECT   a.id, REPLACE (a.combo, '#') combo
  2  FROM    (SELECT id, SYS_CONNECT_BY_PATH (mod, '#') || '#' combo
  3           FROM   mgroup
  4           CONNECT BY PRIOR id = id AND mod > PRIOR mod) a, mgroup b
  5  WHERE    b.id = a.id AND INSTR (a.combo, '#' || b.mod || '#') > 0
  6  GROUP BY a.id, a.combo
  7  HAVING   SUM (b.value) = 45;

 ID COMBO
--- -----
  1 ABC
  1 ABD
  1 ACD
  1 BCD

SQL> SELECT   a.id, REPLACE (a.combo, '#') combo
  2  FROM    (SELECT id, SYS_CONNECT_BY_PATH (mod, '#') || '#' combo
  3           FROM   mgroup
  4           CONNECT BY PRIOR id = id AND mod > PRIOR mod) a, mgroup b
  5  WHERE    b.id = a.id AND INSTR (a.combo, '#' || b.mod || '#') > 0
  6  GROUP BY a.id, a.combo
  7  HAVING   SUM (b.value) = 60;

 ID COMBO
--- -----
  1 ABCD

SQL>

Tom Kyte
February 19, 2004 - 12:38 pm UTC

yeah, thats that nasty cartesian semi join happening.

very nifty solution though, elegant. don't know if I would have thought of connect by in that context - very neat.

fantastic

pete, February 20, 2004 - 3:17 am UTC

Padders, Tom - this is exactly what I was afterm, extra team points all round - cheers!

Thank you Padders and Tom!

Dan Kefford, March 04, 2004 - 9:43 am UTC 

O.K... retrofitting Padders' approach to the first problem that I originally proposed, I arrived at:

SQL> column status_change_path format a30
SQL> WITH all_paths AS
  2  (
  3  SELECT  iv1.person_id,
  4          LTRIM(SYS_CONNECT_BY_PATH (iv1.status_code, '->'), '->') status_change_path,
  5          level path_length
  6  FROM
  7  (
  8  SELECT  h1.person_id,
  9          h1.status_code,
 10          h1.status_date status_date,
 11          LAG(h1.status_date) OVER (PARTITION BY h1.person_id ORDER BY h1.status_date) prev_status_date
 12  FROM    status_history h1
 13  ) iv1
 14  START WITH iv1.prev_status_date IS NULL
 15  CONNECT BY iv1.person_id        = PRIOR iv1.person_id
 16  AND        iv1.prev_status_date = PRIOR iv1.status_date
 17  )
 18  SELECT  iv3.status_change_path,
 19          COUNT(*)
 20  FROM
 21  (
 22  SELECT  iv2.person_id,
 23          iv2.status_change_path
 24  FROM    all_paths iv2
 25  WHERE  iv2.path_length =
 26         (SELECT  MAX(iv3.path_length)
 27          FROM    all_paths iv3
 28          WHERE   iv3.person_id = iv2.person_id)
 29  ) iv3
 30 GROUP BY iv3.status_change_path
 31 /

STATUS_CHANGE_PATH             COUNT(*)
------------------------------ --------
A->B->C                               1
A->C->B                               1
B->A->C                               2

This query has the advantage that I do not need to presume the maximum number of state changes, as Tom's original answer does (8). Very cool solution, Padders.

I _do_ realize that the plan for this query is pretty expensive, but the version of this query that I would consider for production purposes would not be run very frequently. 

However, I do have some follow-up questions. 

1) I noticed when building this query, that SYS_CONNECT_BY_PATH appears to generate all possible paths from the points specified by the START WITH clause, e.g. A, A->C, A->C->B. I needed to obtain the full/longest path per each person id, e.g., A->C->B. I accomplished this by MAX'ing out the path length, using LEVEL in the inline view, GROUPing by each person_id. Is there a better way to do this? Or am I misunderstanding how SYS_CONNECT_BY_PATH behaves?

2) This, of course, could also have been done using a PL/SQL function to return the path for each person_id. Is there a real advantage to use SYS_CONNECT_BY_PATH over such a PL/SQL function? What is SYS_CONNECT_BY_PATH typically used for?

Thanks again, Tom, for this forum, and a great opportunity to exchange ideas.

Or maybe...

Padders, March 08, 2004 - 11:47 am UTC 

Dan perhaps I misunderstand your requirements but I suspect a user-defined string aggregate is more appropriate (not to mention efficient) in your case. For information on 'stragg' function used below try a search right here.

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> DROP TABLE status_history;

Table dropped.

SQL> CREATE TABLE status_history (
  2    person_id NUMBER,
  3    status_code VARCHAR2(1),
  4    status_date DATE);

Table created.

SQL> INSERT INTO status_history
  2    SELECT 1, 'A', TO_DATE ('01-JAN-2002', 'DD-MON-YYYY')
  3    FROM   DUAL
  4    UNION ALL
  5    SELECT 1, 'B', TO_DATE ('02-JAN-2002', 'DD-MON-YYYY')
  6    FROM   DUAL
  7    UNION ALL
  8    SELECT 1, 'C', TO_DATE ('03-JAN-2002', 'DD-MON-YYYY')
  9    FROM   DUAL
 10    UNION ALL
 11    SELECT 2, 'A', TO_DATE ('03-JAN-2002', 'DD-MON-YYYY')
 12    FROM   DUAL
 13    UNION ALL
 14    SELECT 2, 'C', TO_DATE ('05-JAN-2002', 'DD-MON-YYYY')
 15    FROM   DUAL
 16    UNION ALL
 17    SELECT 2, 'B', TO_DATE ('06-JAN-2002', 'DD-MON-YYYY')
 18    FROM   DUAL
 19    UNION ALL
 20    SELECT 3, 'B', TO_DATE ('02-JAN-2002', 'DD-MON-YYYY')
 21    FROM   DUAL
 22    UNION ALL
 23    SELECT 3, 'A', TO_DATE ('07-JAN-2002', 'DD-MON-YYYY')
 24    FROM   DUAL
 25    UNION ALL
 26    SELECT 3, 'C', TO_DATE ('08-JAN-2002', 'DD-MON-YYYY')
 27    FROM   DUAL
 28    UNION ALL
 29    SELECT 4, 'B', TO_DATE ('01-JAN-2002', 'DD-MON-YYYY')
 30    FROM   DUAL
 31    UNION ALL
 32    SELECT 4, 'A', TO_DATE ('05-JAN-2002', 'DD-MON-YYYY')
 33    FROM   DUAL
 34    UNION ALL
 35    SELECT 4, 'C', TO_DATE ('08-JAN-2002', 'DD-MON-YYYY')
 36    FROM   DUAL;

12 rows created.

SQL> COMMIT;

Commit complete.

SQL> COLUMN combo FORMAT A20

SQL> SELECT combo, COUNT (*)
  2  FROM  (SELECT person_id, REPLACE (
  3                  stragg (status_code), ',', '->') combo
  4         FROM  (SELECT person_id, status_code
  5                FROM   status_history
  6                ORDER BY status_date)
  7         GROUP BY person_id)
  8  GROUP BY combo;

COMBO                  COUNT(*)
-------------------- ----------
A->B->C                       1
A->C->B                       1
B->A->C                       2

SQL>

Thanks, Padders

Dan Kefford, March 09, 2004 - 5:25 pm UTC

Thanks, Padders. I'll look into it.

Simple Combinations

kan, October 10, 2005 - 6:45 pm UTC

Tom, is there any function in Oracle to get combinations like this:
I will pass an array ('a','b','c')

the resulting array should have following values:

'abc'
'ab'
'ac'
'bc'
'a'
'b'
'c'

My inputs vary [could be 2,3,5, etc..]
I am not sure how to/what to search so i am posting it in this thread.

Thanks,

Tom Kyte
October 10, 2005 - 7:42 pm UTC

we could do it in sql, but I'd suggest writing a function to do it instead in this case.

a similar problem appeared on sql server forum

Mikito Harakiri, October 10, 2005 - 9:25 pm UTC

input:

Category CategoryItem
-------- ------------
Fruit Apple
Fruit Orange
Car Ford
Car Vauxhall
Car Rover
Car Fiat
Animal Cat
Animal Dog
Animal Tortoise

I need to generate all the possible combinations of the 3 categories:

Comb. # Category CategoryItem
------- -------- ------------
1 Fruit Apple
1 Car Ford
1 Animal Cat
2 Fruit Apple
2 Car Ford
2 Animal Dog
...
24 Fruit Orange
24 Car Fiat
24 Animal Tortoise

Here is a hint, factorial based numbering system:

4*3*#Fruits + 4*#Cars + #Animals

Simple Combinations

Kan, October 11, 2005 - 6:48 pm UTC

Tom,
I am trying to write a function to get combinations of all inputs.

Here is a sample code.

declare

v_array dbms_sql.varchar2_table;
v_array_out dbms_sql.varchar2_table;
v_out varchar2(300):='';
m integer:=0;
n integer:=2;
tot_cnt integer:=1;
Begin

v_array(1):='A';
v_array(2):='B';
v_array(3):='C';
v_array(4):='D';
v_array(5):='E';

--v_array_out(1):='ABCDE';

for k in 1..v_array.count
loop
m:=k+1;
for i in m..v_array.count
loop
v_out:=v_out||v_array(i);
end loop;
v_array_out(k):=v_array(k)||v_out;
v_out:='';
end loop;
tot_cnt:=tot_cnt+v_array_out.count;
--dbms_output.put_line(tot_cnt);

for j in 1..v_array_out.count
loop
dbms_output.put_line(v_array_out(j));
--null;
end loop;
End;

This will give me combinations like
ABCDE
BCDE
CDE
DE
E

But i am not able to get more combinations
eg: ACDE
ADE
AD
AE
BDE
BD
BE
E
etc..

Any ideas you can share,is greatly appreciated.
Thanks,

In reply to Kan

Padders, October 12, 2005 - 7:50 am UTC 

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE TYPE varchar2_table AS TABLE OF VARCHAR2 (4000);
  2  /

Type created.

SQL> SELECT REPLACE (SYS_CONNECT_BY_PATH (
  2           column_value, '|'), '|') combination 
  3  FROM   TABLE (varchar2_table ('A', 'B', 'C')) 
  4  CONNECT BY column_value > PRIOR column_value;

COMBINATION
--------------------------------------------------------------------------------
A
AB
ABC
AC
B
BC
C

7 rows selected.

SQL>

In reply to Mikito

Padders, October 12, 2005 - 7:54 am UTC 

I came up with this but it is both proprietary and somewhat inelegant.

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> SELECT combination, 
  2         SUBSTR (column_value, 1, INSTR (column_value, '/') - 1) category, 
  3         SUBSTR (column_value, INSTR (column_value, '/') + 1) categoryitem 
  4  FROM  (SELECT ROWNUM combination, 
  5                SUBSTR (SYS_CONNECT_BY_PATH ( 
  6                  category || '/' || categoryitem, '|'), 2) category 
  7         FROM   table_name 
  8         WHERE  LEVEL = 3 
  9         CONNECT BY category > PRIOR category), 
 10         TABLE (string_to_table (category, '|'));

COMBINATION CATEGORY             CATEGORYITEM
----------- -------------------- --------------------
          1 Animal               Cat
          1 Car                  Ford
          1 Fruit                Apple
          2 Animal               Cat
          2 Car                  Ford
          2 Fruit                Orange
          3 Animal               Cat
          3 Car                  Vauxhall
          3 Fruit                Apple
(...)
         21 Animal               Tortoise
         21 Car                  Rover
         21 Fruit                Apple
         22 Animal               Tortoise
         22 Car                  Rover
         22 Fruit                Orange
         23 Animal               Tortoise
         23 Car                  Fiat
         23 Fruit                Apple
         24 Animal               Tortoise
         24 Car                  Fiat
         24 Fruit                Orange

72 rows selected.

SQL>

to Padders from UK

A reader, October 12, 2005 - 11:29 am UTC

Thanks. This is what i was looking for.

Alternative SQL solution for the "Sum & Combination" question

Frank Zhou, December 08, 2006 - 11:34 am UTC 

     Here is an alternative SQL solution for the question  posted by 'Pete' on February 19, 2004. This solution can skp the join back to the mgroup table. 

Frank

SQL> SELECT  id , path
  2  FROM
  3  (SELECT id, SYS_CONNECT_BY_PATH (value, ',') || ',' as str,
  4    REPLACE(SYS_CONNECT_BY_PATH (mod, ','), ',')  as path
  5     FROM   mgroup
  6     CONNECT BY NOCYCLE PRIOR id = id AND mod > PRIOR mod
  7  )
  8  CONNECT BY PRIOR path = path
  9  AND INSTR (str, ',', 1, LEVEL+1) > 0
 10  AND PRIOR dbms_random.string ('p', 10) IS NOT NULL
 11  GROUP BY id,  path
 12  having SUM(TO_NUMBER( SUBSTR(str,
 13                         INSTR(str, ',', 1, LEVEL  ) + 1,
 14                         INSTR(str, ',', 1, LEVEL+1) -
 15           INSTR (str, ',', 1, LEVEL) -1 ) )) = 45;

        ID  PATH                                                                       
----------  -----------                                                                                                                     

         1   ABC                                                                   
                                                                                                                                                 
         1   ABD                                                                    
                                                                                                                                                 
         1   ACD                                                                    
                                                                                                                                               
         1   BCD                                                                   
                                                                                                                 
SQL> spool off

riyaz, August 06, 2008 - 1:38 am UTC

I do have same scenario. I used the method prescribed above, it worked. But the requirement is little complex that number of entries are dynamic.
Input
Question-id answer-id
1 1
1 2
1 3
2 4
2 5
2 6
3 7
3 8
3 9

output required is:
- combination of answer from each question.
- same answer will not come for different question for sure
- combination should not repeat.
- number of questions and number of answers for each question can differ. it can be any no. of qns also.
output should be
1
4
7

1
4
8

1
4
9

1
5
7
etc...
totally 27 combinations for the above

could you pl help. I searched your site for last 1 week to get the logic, but in vain.

Tom Kyte
August 06, 2008 - 8:49 am UTC

no create table
no inserts
no look


but basically, it doesn't sound logical to do this in sql.

you'd need to cartesian join each question with every other question - you'd need to know the number of questions.

Also, if you say things like "and number of answers for each question can differ. ", you should really really craft an example that DOES THAT, your example should contain all of the possible ways things could be

can it be done through plsql

riyaz, August 07, 2008 - 1:19 am UTC

Sorry for not providing proper inputs.

create table input (qid number, aid number);
create table output (aid number, cseq number);

input data:
insert into input values (1,1);
insert into input values (1,2);
insert into input values (1,3);
insert into input values (2,4);
insert into input values (2,5);
insert into input values (2,6);
insert into input values (3,7);
insert into input values (3,8);
commit;


output data required
--------------------
(combination set number, answer combination)
(no question is required in output)
number of rows output: 3 answers *3 answers * 2 answers = 18 combinations


1 1
1 4
1 7

2 1
2 4
2 8

3 1
3 5
3 7

4 1
4 5
4 8

5 1
5 6
5 7

6 1
6 6
6 8

7 2
7 4
7 7

8 2
8 4
8 8

9 2
9 5
9 7


10 2
10 5
10 8

11 2
11 6
11 7

12 2
12 6
12 8


13 3
13 4
13 7

14 3
14 4
14 8

15 3
15 5
15 7

16 3
16 5
16 8

17 3
17 6
17 7

18 3
18 6
18 8

I am just following your mantra: 1- Do it with SQL 2-If not, Do it with PLSQL 3-If not, Do it with Java 4- If not, Do it with C 5-Otherwise do not do it.
If it cannot be done through SQL. Can it be done through PLSQL.

Total number of combination is basically (1st answer count ) * (2nd answer count) * (3rd answer count) * etc..

In PLSQL if we know for sure the number of questions e.g, 3, it can be done with 3 loops (or cursors). since total number of question is not known, can it be done through recursive function calling. I tried but not able to achieve the result. Pl help.

Note: Result should also have some sequence number as mentioned in the output, to identify each set. This is basically for getting different answer combination for survey (questions), awarding points based on different answer combination.



Tom Kyte
August 07, 2008 - 1:22 pm UTC

sorry, i don't know why 1,5 is not in there.

and many others.

I don't understand your output

riyaz, August 08, 2008 - 6:21 am UTC

Sorry to disturb you again.
in the output, pl ignore the first column (which is actually the sequence number for each set of output. IT IS NOT QUESTION NUMBER)
if you see the 2nd column, which is answer combination. That is what required as output. 18 Answers combinations. each answer combination should have unique sequence number. there is no question no is required in the output.


Tom Kyte
August 08, 2008 - 1:18 pm UTC

I'll go back to this then

... Also, if you say things like "and number of answers for each question can differ. ", you should really really craft an example that DOES THAT, your example should contain all of the possible ways things could be ...

you have not, in my opinion, specified this out to a point that one could write code from - not without you coming back and saying "well that works, but not for this case... and then later and that works for the first cases but not this case..."

full specification, complete example, create tables, inserts - all self contained in one place (do not make us page up and down and try to rebuild the script needed to do this)

full sample code is given. pl help

riyiaz, August 11, 2008 - 1:09 am UTC

create table input (qid number, aid number);
create table output (aid number, cseq number);

input data:
insert into input values (101,1);
insert into input values (101,2);
insert into input values (101,3);
insert into input values (102,4);
insert into input values (102,5);
insert into input values (102,6);
insert into input values (103,7);
insert into input values (103,8);
insert into input values (103,9);
commit;


output data required
--------------------
(combination set number, answer combination)
(no question is required in output)
question numbers 101, 102, 103 and each question has 3 answers

number of rows output: 3 answers *3 answers * 3 answers = 27 answers combinations
I am getting 27 combinations here as output. Same procedure does not work if number of questions is 4. I need to go for 4 loops. I need to create a procedure, similar to this, which can work even if questions are any numbers

my proc:
--------
declare
tseq number:=0;
begin
delete from ta;
commit;
for c1 in (select qid, aid from t1 where qid=101 order by aid)
loop
for c2 in (select qid, aid from t1 where qid = 102 order by qid, aid)
loop
---------3rd loop

for c3 in (select aid from t1 where qid = 103 order by qid, aid)
loop
tseq:= tseq+1;
insert into ta(cseq, aid) values (tseq, c1.aid);
insert into ta(cseq, aid) values (tseq, c2.aid);
insert into ta(cseq, aid) values (tseq, c3.aid);
-- dbms_output.put_line(c1.aid);
-- dbms_output.put_line(c2.aid);
-- dbms_output.put_line(c3.aid);

end loop;
-- dbms_output.put_line(tseq);
dbms_output.put_line('----------');
------------3rd loop end
end loop;
end loop;
commit;
end;
/


OUTPUT generated
----------------
first column cseq is the running sequence number to identify each answer combination set.

1* select cseq seq_order, aid from ta order by cseq, aid
SQL> /
1 1
1 4
1 7
2 1
2 4
2 8
3 1
3 4
3 9
4 1
4 5
4 7
5 1
5 5
5 8
6 1
6 5
6 9
7 1
7 6
7 7
8 1
8 6
8 8
9 1
9 6
9 9
10 2
10 4
10 7
11 2
11 4
11 8
12 2
12 4
12 9
13 2
13 5
13 7
14 2
14 5
14 8
15 2
15 5
15 9
16 2
16 6
16 7
17 2
17 6
17 8
18 2
18 6
18 9
19 3
19 4
19 7
20 3
20 4
20 8
21 3
21 4
21 9
22 3
22 5
22 7
23 3
23 5
23 8
24 3
24 5
24 9
25 3
25 6
25 7
26 3
26 6
26 8
27 3
27 6
27 9
Tom Kyte
August 12, 2008 - 8:19 am UTC

You would need to know how many questions there are, you would need to cartesian join that many times.

so you would need to query to find out how many, then query using a cartesian join.

select *
  from input t1, input t2, input t3
 where t1.qid < t2.qid and t2.qid < t3.qid
/

select *
  from input t1, input t2, input t3, input t4
 where t1.qid < t2.qid and t2.qid < t3.qid and t3.qid < t4.qid
/


there are the queries for 3 and 4, but you need to know the cardinality of qid and then dynamically construct the query.

correction

riyiaz, August 11, 2008 - 1:12 am UTC

create table t1 (qid number, aid number);
create table ta (aid number, cseq number);

pl use the above code for table creation instead of input & output table.

riyiaz, August 11, 2008 - 1:13 am UTC

create table t1 (qid number, aid number);
create table ta (aid number, cseq number);

input data:
insert into t1 values (101,1);
insert into t1 values (101,2);
insert into t1 values (101,3);
insert into t1 values (102,4);
insert into t1 values (102,5);
insert into t1 values (102,6);
insert into t1 values (103,7);
insert into t1 values (103,8);
insert into t1 values (103,9);
commit;
pl use this code for creating table and data.

great!!!

riyaz, August 13, 2008 - 5:30 am UTC

Gorgeous answer. It worked for me.

New answer to original question 1)

Stew Ashton, August 27, 2016 - 5:16 am UTC

Since this question has been "promoted" on twitter, I thought it would be fun to see what can be done in more recent versions of Oracle. This solution requires version 11.2
with status_history(person_id, status_code, status_date) as (
  SELECT 1, 'A', TO_DATE('01-JAN-2002', 'DD-MON-YYYY') FROM DUAL 
  UNION ALL SELECT 1, 'B', TO_DATE('02-JAN-2002', 'DD-MON-YYYY') FROM DUAL 
  UNION ALL SELECT 1, 'C', TO_DATE('03-JAN-2002', 'DD-MON-YYYY') FROM DUAL 
  UNION ALL SELECT 2, 'A', TO_DATE('03-JAN-2002', 'DD-MON-YYYY') FROM DUAL 
  UNION ALL SELECT 2, 'C', TO_DATE('05-JAN-2002', 'DD-MON-YYYY') FROM DUAL 
  UNION ALL SELECT 2, 'B', TO_DATE('06-JAN-2002', 'DD-MON-YYYY') FROM DUAL 
  UNION ALL SELECT 3, 'B', TO_DATE('02-JAN-2002', 'DD-MON-YYYY') FROM DUAL 
  UNION ALL SELECT 3, 'A', TO_DATE('07-JAN-2002', 'DD-MON-YYYY') FROM DUAL 
  UNION ALL SELECT 3, 'C', TO_DATE('08-JAN-2002', 'DD-MON-YYYY') FROM DUAL 
  UNION ALL SELECT 4, 'B', TO_DATE('01-JAN-2002', 'DD-MON-YYYY') FROM DUAL 
  UNION ALL SELECT 4, 'A', TO_DATE('05-JAN-2002', 'DD-MON-YYYY') FROM DUAL 
  UNION ALL SELECT 4, 'C', TO_DATE('08-JAN-2002', 'DD-MON-YYYY') FROM DUAL 
) 
select distinct
listagg(status_code, '-') within group(order by status_date) status_hist
from status_history
group by person_id;

STATUS_HIST
-----------
A-B-C
A-C-B
B-A-C

Connor McDonald
August 28, 2016 - 7:19 am UTC

Nice addition to the thread.

New answer to original question 2)

Stew Ashton, August 27, 2016 - 5:27 am UTC

Since this thread has been "promoted" on twitter, I thought it would be fun to see what can be done in more recent versions of Oracle. I think CONNECT_BY_ISLEAF requires version 11.1 or 11.2, I can't remember which.
CREATE TABLE thing_attributes 
 (thing_id NUMBER, 
 attribute_code VARCHAR2(1)) 
/ 
INSERT INTO thing_attributes 
SELECT 1, 'A' FROM DUAL 
UNION ALL SELECT 1, 'B' FROM DUAL 
UNION ALL SELECT 2, 'B' FROM DUAL 
UNION ALL SELECT 2, 'C' FROM DUAL 
UNION ALL SELECT 2, 'D' FROM DUAL 
UNION ALL SELECT 3, 'A' FROM DUAL 
UNION ALL SELECT 3, 'D' FROM DUAL 
UNION ALL SELECT 4, 'D' FROM DUAL 
/ 
CREATE TABLE belongings 
 (person_id NUMBER, 
 thing_id NUMBER) 
/
INSERT INTO belongings 
 SELECT 100, 1 FROM DUAL 
 UNION ALL SELECT 100, 2 FROM DUAL 
 UNION ALL SELECT 100, 3 FROM DUAL 
 UNION ALL SELECT 100, 4 FROM DUAL 
 UNION ALL SELECT 200, 1 FROM DUAL 
 UNION ALL SELECT 200, 2 FROM DUAL 
 UNION ALL SELECT 200, 4 FROM DUAL 
/ 

with data as (
  select person_id, thing_id, attribute_code,
  dense_rank() over(partition by person_id order by thing_id) dr
  from thing_attributes join belongings using(thing_id)
)
select person_id,
sys_connect_by_path(attribute_code, '-') path
from data
where connect_by_isleaf = 1
start with dr = 1
connect by person_id = prior person_id
and dr = prior dr + 1
order by 1,2;

PERS PATH
100  -A-B-A-D
100  -A-B-D-D
100  -A-C-A-D
100  -A-C-D-D
100  -A-D-A-D
100  -A-D-D-D
100  -B-B-A-D
100  -B-B-D-D
100  -B-C-A-D
100  -B-C-D-D
100  -B-D-A-D
100  -B-D-D-D
200  -A-B-D
200  -A-C-D
200  -A-D-D
200  -B-B-D
200  -B-C-D
200  -B-D-D

New answer to the "mgroup" question

Stew Ashton, August 27, 2016 - 5:55 am UTC

Bookmarks to reviews don't seem to work right now. Oracle version 11.2 brought CTEs; here they allow me to stop recursing when a sum is reached.
drop table mgroup purge;
create table mgroup (id number, mod varchar2(8), value number);

insert into mgroup values (1, 'A', 15);
insert into mgroup values (1, 'B', 15);
insert into mgroup values (1, 'C', 15);
insert into mgroup values (1, 'D', 15);

with recurse(id, mod, agg_mod, value) as (
  select id, mod, mod, value from mgroup
  union all
  select n.id, n.mod, o.agg_mod||n.mod, n.value+o.value
  from mgroup n 
  join recurse o on n.id = o.id and n.mod > o.mod and n.value+o.value <= 30
)
select agg_mod from recurse
where value = 30;

AGG_MOD
-------
AD
AC
AB
BD
BC
CD

New answer to Mikito's question

Stew Ashton, August 27, 2016 - 2:34 pm UTC

This question never got answered. The solution here uses the LATERAL clause which is new in version 12c. The idea is to find every combination where you take one item from each category. For example, take one each of 2 fruits, 4 cars and 3 animals and you will have 2*4*3 = 24 combinations.
with data(Category,  CategoryItem) as ( select
  'Fruit', 'Apple' from dual union all select
  'Fruit', 'Orange' from dual union all select
  'Car', 'Ford' from dual union all select
  'Car', 'Vauxhall' from dual union all select
  'Car', 'Rover' from dual union all select
  'Car', 'Fiat' from dual union all select
  'Animal', 'Cat' from dual union all select
  'Animal', 'Dog' from dual union all select
  'Animal', 'Tortoise' from dual
)
, groups as (
  select category,
  count(*) item_cnt,
  round(exp(
    sum(ln(count(*))) over()
  )) all_factors,
  nvl(
    round(exp(
      sum(ln(count(*))) over(order by category rows between 1 following and unbounded following)
    )),
    1
  ) later_factors
  from data
  group by category
)
, combos as (
  select * from groups,
  lateral (
    select level combo, 
    mod(floor((level-1)/later_factors),item_cnt) item_rank
    from dual
    connect by level <= all_factors
  )
)
, ranked_data as (
  select data.*,
    row_number() over (partition by category order by categoryitem)-1 item_rank
  from data
)
select combo, category, categoryitem
from ranked_data
join combos using(category, item_rank)
order by 1,2,3;

COMBO CATEGORY CATEGORYITEM
1     Animal   Cat
1     Car      Fiat
1     Fruit    Apple
2     Animal   Cat
2     Car      Fiat
2     Fruit    Orange
3     Animal   Cat
3     Car      Ford
3     Fruit    Apple
4     Animal   Cat
4     Car      Ford
4     Fruit    Orange
5     Animal   Cat
5     Car      Rover
5     Fruit    Apple
6     Animal   Cat
6     Car      Rover
6     Fruit    Orange
7     Animal   Cat
7     Car      Vauxhall
7     Fruit    Apple
8     Animal   Cat
8     Car      Vauxhall
8     Fruit    Orange
9     Animal   Dog
9     Car      Fiat
9     Fruit    Apple
10    Animal   Dog
10    Car      Fiat
10    Fruit    Orange
11    Animal   Dog
11    Car      Ford
11    Fruit    Apple
12    Animal   Dog
12    Car      Ford
12    Fruit    Orange
13    Animal   Dog
13    Car      Rover
13    Fruit    Apple
14    Animal   Dog
14    Car      Rover
14    Fruit    Orange
15    Animal   Dog
15    Car      Vauxhall
15    Fruit    Apple
16    Animal   Dog
16    Car      Vauxhall
16    Fruit    Orange
17    Animal   Tortoise
17    Car      Fiat
17    Fruit    Apple
18    Animal   Tortoise
18    Car      Fiat
18    Fruit    Orange
19    Animal   Tortoise
19    Car      Ford
19    Fruit    Apple
20    Animal   Tortoise
20    Car      Ford
20    Fruit    Orange
21    Animal   Tortoise
21    Car      Rover
21    Fruit    Apple
22    Animal   Tortoise
22    Car      Rover
22    Fruit    Orange
23    Animal   Tortoise
23    Car      Vauxhall
23    Fruit    Apple
24    Animal   Tortoise
24    Car      Vauxhall
24    Fruit    Orange

To Stew | on CONNECT_BY_ISLEAF

Rajeshwaran Jeyabal, August 29, 2016 - 10:44 am UTC

I think CONNECT_BY_ISLEAF requires version 11.1 or 11.2, I can't remember which.

No, CONNECT_BY_ISLEAF is available since 10g

demo@ORA10G> column x format a20
demo@ORA10G> select sys_connect_by_path(deptno,',') x
  2  from dept
  3  where connect_by_isleaf=1
  4  start with deptno =1
  5  connect by prior deptno = deptno-1 ;

X
--------------------
,1,2,3,4,5

1 row selected.

demo@ORA10G> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

5 rows selected.

demo@ORA10G>

Yet another idea to Mikito's question

Oren Nakdimon (@DBoriented), August 29, 2016 - 12:19 pm UTC

Should work in 10g and above.

create type cat_item_t as object (category varchar2(100), item varchar2(100))
/

create type cat_item_ntt as table of cat_item_t
/

with data(Category,  CategoryItem) as ( select
  'Fruit', 'Apple' from dual union all select
  'Fruit', 'Orange' from dual union all select
  'Car', 'Ford' from dual union all select
  'Car', 'Vauxhall' from dual union all select
  'Car', 'Rover' from dual union all select
  'Car', 'Fiat' from dual union all select
  'Animal', 'Cat' from dual union all select
  'Animal', 'Dog' from dual union all select
  'Animal', 'Tortoise' from dual
)
select listagg(item, ', ') within group(order by category)
from   (select rownum combo,column_value,n
        from   (select cast(collect(cat_item_t(category, categoryitem)) as cat_item_ntt) x,
                       count(distinct category) n
                from   data),
               table(POWERMULTISET_BY_CARDINALITY(x, n))),
       table(column_value)
group  by combo,n
having count(distinct category) = n
order by 1;

LISTAGG(ITEM,',')WITHINGROUP(ORDERBYCATEGORY)
--------------------------------------------------------------------------
Cat, Fiat, Apple
Cat, Fiat, Orange
Cat, Ford, Apple
Cat, Ford, Orange
Cat, Rover, Apple
Cat, Rover, Orange
Cat, Vauxhall, Apple
Cat, Vauxhall, Orange
Dog, Fiat, Apple
Dog, Fiat, Orange
Dog, Ford, Apple
Dog, Ford, Orange
Dog, Rover, Apple
Dog, Rover, Orange
Dog, Vauxhall, Apple
Dog, Vauxhall, Orange
Tortoise, Fiat, Apple
Tortoise, Fiat, Orange
Tortoise, Ford, Apple
Tortoise, Ford, Orange
Tortoise, Rover, Apple
Tortoise, Rover, Orange
Tortoise, Vauxhall, Apple
Tortoise, Vauxhall, Orange

24 rows selected.

Yet another solution

Iudith Mentzel, August 31, 2016 - 12:07 am UTC

For the sake of completeness, here is just another solution, for 11gR2 and higher:

with data(Category, CategoryItem) as ( select
'Fruit', 'Apple' from dual union all select
'Fruit', 'Orange' from dual union all select
'Car', 'Ford' from dual union all select
'Car', 'Vauxhall' from dual union all select
'Car', 'Rover' from dual union all select
'Car', 'Fiat' from dual union all select
'Animal', 'Cat' from dual union all select
'Animal', 'Dog' from dual union all select
'Animal', 'Tortoise' from dual
),
cat_group as (
select category,
row_number() over (order by category) cat_rn,
count(*) over () cat_all
from data
group by category
),
recur (comb, cat_rn, cat_all) as (
select cast(d.categoryItem as varchar2(100)), c.cat_rn, c.cat_all
from data d,
cat_group c
where c.category = d.category
and c.cat_rn = 1
union all
select cast( (r.comb||', '||d.categoryItem) as varchar2(100) ), c.cat_rn, c.cat_all
from recur r,
data d,
cat_group c
where c.cat_rn = r.cat_rn + 1
and d.category = c.category
)
select comb
from recur
where cat_rn = cat_all
order by comb
/


and, yet another variant of it:

with data(Category, CategoryItem) as ( select
'Fruit', 'Apple' from dual union all select
'Fruit', 'Orange' from dual union all select
'Car', 'Ford' from dual union all select
'Car', 'Vauxhall' from dual union all select
'Car', 'Rover' from dual union all select
'Car', 'Fiat' from dual union all select
'Animal', 'Cat' from dual union all select
'Animal', 'Dog' from dual union all select
'Animal', 'Tortoise' from dual
),
recur (comb, category) as (
select cast(d.categoryItem as varchar2(100)), d.category
from data d
where d.category = (select min(category) from data)
union all
select cast( (r.comb||', '||d.categoryItem) as varchar2(100) ), d.category
from recur r,
data d
where d.category = (select min(category) from data where category > r.category)
)
cycle comb set x to 'Y' default 'N'
select comb
from recur
where category = (select max(category) from data)
order by comb
/

This second one is one of those "nasty" cases that require a CYCLE clause to be included,
to avoid the "ORA-32044: cycle detected while executing recursive WITH query",
though, effectively there are no cycles here.

By the way, LISTAGG also works only for 11gR2 and higher.

Recursion "rocks and rolls", is what Tom Kyte would probably say :):)


question

m.manoj kumar, March 02, 2020 - 12:53 pm UTC

in a table there is an only one column with id values are a,b,c,d,e .
i want output as ab,bc,cd,de.
how to get this output and what is the syntax to use
Chris Saxon
March 02, 2020 - 2:58 pm UTC

There are lots of tricks you could use. But lag/lead are probably the best. Giving something like:

c1 || lead ( c1 ) over ( order by  c1 )


In your select. You'll also need to filter out the last row (where lead is null).
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.