permutations of data in PL/SQL

[Johan Louwers]

Hi,
I have a question I would like to do permutations on a set of data I
get from a SQL query. Let say I do the following SQL query

Select * from mytable;

I get the following result:

A,B,C

I would however like to get back the following:

A,B,C
A,C,B
B,A,C
B,C,A
C,A,B
C,B,A

Anybody knows how I can do something like this by using only SQL | PL/
SQL

Thanks already.
Regards,
Johan Louwers.

[sonty]

:=O

[rob wolfe]

I would write a table function that did the permutation for me. For
details on Table functions see ...

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/08_subs.htm#ADDCI012

I am sure someone might have a better idea (waiting for mike to wade in
with a magical xml version ;-)) but I write a fair number of table
functions and they are pretty easy to do .

[Johan Louwers]

Hi,
I received an answer on this question somewhere else stating the
following approach: table teams has 1 column and 3 records; A, B & C)

select t1.id,t2.id,t3.id from teams t1,teams t2,teams t3
where t2.id not in (t1.id) and t3.id not in (t1.id,t2.id)
order by t1.id,t2.id,t3.id;


However, this only go’s for a known number of records returned, is
there some way we can change this to a procedure that will handle X
number of returning records?

Regards,
JohanLouwers.

On Apr 28, 6:29 am, rob wolfe <rob.wo...@oRAclegeeks.com> wrote:
> I would write a table function that did the permutation for me. For
> details on Table functions see ...
>

> http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/08_sub...

[rob wolfe]

Johan

Can you give a clear, concise, consistent statement of your problem? So
far you have actually posed two different although related problems.

1)What is it (in non-tech terms) that you are actually trying to
accomplish? In english as opposed to just an example .. but an example
is always nice as an addition. If we know the business reason that you
are trying to accomplish something there is sometimes another way to get
there than the obvious and the more info we have the more we can help.
2)What is the table structure you are using
3) What does your data look like (vaguely .. doesnt have to be precise)
4)do you have the ability to create functions (and types) in your
database (don't assume you do .. many DBAs lock this out)
5) do you need a "pure sql query" solution or will a mix of PL/SQL (a
function for instance) and a query do?

I will bet that if you do that then you will get a myriad of responses

Rob

[Johan Louwers]

Hi Rob,
Sorry for the confusion. Lets get this all straight by this post.

(1)What I am trying to accomplish:
I like to get all the possible combinations of multiply records
(single column) into several records (multiply column) For example I
do have a table named teams in my database and if I do a select on it
I will get the following returned.

SQL> select * from teams;

ID
--
A
B
C
D
E
F
G

7 rows selected

Now I like to have all possible combinations of A,B,C,D,E,F,G. Meaning
the function / query / ?? should return something like this:

1 A B C D E F G
2 A B C D E G F
3 A B C D F E G
4 A B C D F G E
5 A B C D G E F
6 A B C D G F E
7 A B C E D F G
8 A B C E D G F
9 A B C E F D G
10 etc etc etc


The business reason for them to have this is that they would like to
know all possible combinations of a “team” of items and in which
sequence they can insert it into the final process. With the
possibility list planners are continuing in a following step. The
reason however is not that much of a issue I think,… we just need a
list of all possible combinations you can form in a way you can see
above here :-)

(2)Currently we have as a test a table with a single column which
contains the test data A,B,C, etc etc… when we have this part finished
we will replace the table with a view which will return a single
column with only the active options.

(3)The future data will be varchar2 data +/- 6 to 10 chars long.

(4)Do you have the ability to create functions (and types) in your
database (don't assume you do .. many DBAs lock this out)? Yes we
have, I have the ability to create functions, procedures, views,
tables.. all so no limitation on this side.. however we would like to
completely create by using SQL PL/SQL and not by invoking an other
language like for example java.

(5)Aha ;-) answer 4 :-)

Now as I already posted a example I will get back to that a little.
The example I posted is doing the trick a little.

select t1.id,t2.id,t3.id from teams t1,teams t2,teams t3
where t2.id not in (t1.id) and t3.id not in (t1.id,t2.id)
order by t1.id,t2.id,t3.id;

This will return:
1 A B C
2 A C B
3 B A C
4 B C A
5 C A B
6 C B A

However this query will only work if I only have A,B,C in my table. It
should work and react as the input table is like:
SQL> select * from teams;

ID
--
A
B
C
D
E
F
G

7 rows selected

Then it should return automatically:

1 A B C D E F G
2 A B C D E G F
3 A B C D F E G
4 A B C D F G E
5 A B C D G E F
6 A B C D G F E
7 A B C E D F G
8 A B C E D G F
9 A B C E F D G
10 etc etc etc


Is this post making things a little more clear?

Regards,
Johan Louwers.

[Johan Louwers]

To make sure things are clear, When I state the following:

1 A B C D E F G
2 A B C D E G F
3 A B C D F E G
4 A B C D F G E
5 A B C D G E F
6 A B C D G F E
7 A B C E D F G
8 A B C E D G F
9 A B C E F D G
10 etc etc etc

I mean:
ID X1 X2 X3 X4 X5 X6 X7
-- -- -- -- -- -- -- --

1 A B C D E F G
2 A B C D E G F
3 A B C D F E G
4 A B C D F G E
5 A B C D G E F
6 A B C D G F E
7 A B C E D F G
8 A B C E D G F
9 A B C E F D G
10 etc etc etc

meaning that they all have there own column and it is not a long
single string with tabs between it... (just to make sure there is no
Confusion)

[sonty]

The solution I am providing I dont think is very good.
but i think this will do what you want.
First you need to create one procedure:-
________________________________________
SQL>CREATE OR REPLACE PROCEDURE teams_comb
AS
v_select VARCHAR2 (1000) := NULL;
v_table VARCHAR2 (1000) := NULL;
v_create VARCHAR2 (1000) := NULL;
v_from VARCHAR2 (1000) := NULL;
v_where VARCHAR2 (1000) := NULL;
v_count NUMBER := 0;
i NUMBER;
v_query VARCHAR2 (4000) := NULL;
BEGIN
SELECT COUNT (*)
INTO v_count
FROM teams;

DBMS_OUTPUT.put_line (v_count);

FOR i IN 1 .. v_count
LOOP
IF i <> v_count
THEN
IF i <> 1
THEN
v_where :=
v_where
|| 't'
|| i
|| '.id not in ('
|| SUBSTR (v_select, 1, LENGTH (v_select) - 1)
|| ') and ';
END IF;

v_select := v_select || 't' || i || '.id,';
v_from := v_from || 'teams t' || i || ',';
v_create := v_create || 'column' || i || ' varchar2(10),';
ELSE
v_where :=
v_where
|| 't'
|| i
|| '.id not in ('
|| SUBSTR (v_select, 1, LENGTH (v_select) - 1)
|| ') ';
v_select := v_select || 't' || i || '.id ';
v_from := v_from || 'teams t' || i || ' ';
v_create := v_create || 'column' || i || ' varchar2(10)';
END IF;
END LOOP;

v_query :=
'SELECT '
|| v_select
|| 'FROM '
|| v_from
|| 'WHERE '
|| v_where
|| 'ORDER BY '
|| v_select;

BEGIN
SELECT '1'
INTO v_table
FROM all_tables
WHERE table_name LIKE 'RESULT_TABLE';

EXECUTE IMMEDIATE 'drop table RESULT_TABLE';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('NO Result table');
END;

EXECUTE IMMEDIATE 'Create table RESULT_TABLE ( ' || v_create ||
' )';

EXECUTE IMMEDIATE 'Insert into RESULT_TABLE ' || v_query;
COMMIT;

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
/


Then whenever you want the permutations just run this procedure
first:-
-----------------------------------------------------------------------
SQL>BEGIN
teams_comb;
END;
/

and then run this query:-
------------------------
SQL>SELECT * FROM result_table;

Regards,
Sonty

> > > >>> Johan Louwers.- Hide quoted text -
>
> - Show quoted text -

[Michael Moore]

I think what you want is multiset or powermultiset. A complete example, including test data, can be see in the article entitled "Powermultiset to find combinations"
at http://plsqlnotes.blogspot.com/

Here is an example, and you might find better examples online.

SELECT  DISTINCT  oidt3, SUM ( price ) possible
    FROM ( SELECT oidt3, oiidt3
                , ( SELECT unit_price
                     FROM demo_order_items
                    WHERE order_id = oidt3
                      AND order_item_id = oiidt3 ) price
                , CAST
                     ( ( SELECT XMLELEMENT
                                    ( "D"
                                    , XMLAGG ( XMLELEMENT ( "E"
                                                          , e.order_item_id ) ) ).getclobval
                                                                      ( )
                                                                     AS "Order_Id"
                          FROM TABLE ( xx ) e ) AS VARCHAR2 ( 500 ) ) myxml
            FROM ( SELECT t1.order_id myid, VALUE ( t2 ) xx, t3.order_id oidt3
                        , t3.order_item_id oiidt3
                    FROM ( SELECT  order_id order_id
                                 , CAST
                                      ( COLLECT ( order_line_typ ( order_id
                                                                 , order_item_id ) ) AS order_line_tab_typ )
                                                                                      OID
                              FROM demo_order_items
                          GROUP BY order_id ) t1
                       , TABLE ( POWERMULTISET ( t1.OID ) ) t2
                       , TABLE ( VALUE ( t2 ) ) t3 ) )
GROUP BY oidt3
       , myxml
ORDER BY 1,2;

[Johan Louwers]

Sonty,
I think this something I can use. I will try chaging the code somewhat
so I better fit my needs however this is a very good starting
point.... :-) Thanks... I will be posting the final code when I am
done so you guys can see what the end product has become,... or when I
have a question I will also post it here :-)

Regards,
Johan Louwers.

> ...
>
> read more »

[Michael Moore]

Sorry about the previous response, I didn't actually examine the question.

Here is a query that will give the results you desire.

WITH mycat AS
     (SELECT     tab.a,
                 LEVEL lvl,
                 SYS_CONNECT_BY_PATH (a, ',') combo
            FROM (SELECT 'a' a
                    FROM DUAL
                  UNION ALL
                  SELECT 'b' a
                    FROM DUAL
                  UNION ALL
                  SELECT 'c' a
                    FROM DUAL) tab
      CONNECT BY NOCYCLE PRIOR a != a)
SELECT substr(combo,2) combo
  FROM mycat
 WHERE lvl = (SELECT MAX (lvl)
                FROM mycat);

You would need to make a little tweek. Your table presented 3 columns. You would need to pivot that data into 3 rows. The FROM dual stuff simulates those 3 rows.

Mike

[Michael Moore]

A small glitch. It won't work correctly when the same value is in 2 or more columns. You can easily correct this problem by prefixing your data value with rownum as shown below. It's a small matter to filter out the rownum from the final result of this query

WITH mycat AS
     (SELECT     tab.a,
                 LEVEL lvl,
                 SYS_CONNECT_BY_PATH (a, ',') combo

            FROM (SELECT ROWNUM || my.a a

                    FROM (SELECT 'a' a
                            FROM DUAL
                          UNION ALL
                          SELECT 'b' a
                            FROM DUAL
                          UNION ALL

                          SELECT 'd' a
                            FROM DUAL
                          UNION ALL
                          SELECT 'd' a
                            FROM DUAL) my) tab

      CONNECT BY NOCYCLE PRIOR a != a)

SELECT SUBSTR (combo, 2) combo