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.
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
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>
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,
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.
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.
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.
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
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
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
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).