Home » SQL & PL/SQL » SQL & PL/SQL » create dynamic groups like; ABC, BCA, CAB, CBA...
| create dynamic groups like; ABC, BCA, CAB, CBA... [message #176855] |
Sat, 10 June 2006 10:02  |
mallioracle
Messages: 2
Registered: June 2006
Location: US
|
Junior Member |
|
|
I have two columns like, treatcode and groups
If treatcode is 2 (A,B) then groups column should be
AB, BA
If treatcode is 3 (A,B,C) then groups column shoud be
ABC
BCA
CAB
CBA
BAC
ACB
If treatcode is 4 (A,B,C,D) then groups column shoud be
ABCD
BCDA
CDAB
DABC
........
Please let me know is there any good approach for this?
Thanks
malli.
|
|
|
|
| Re: create dynamic groups like; ABC, BCA, CAB, CBA... [message #176949 is a reply to message #176855] |
Mon, 12 June 2006 03:17  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I don't know about a good solution, but I've got a solution
create table temp_mix (col_1 varchar2(1),col_2 varchar2(1),col_3 varchar2(1));
insert into temp_mix values ('A','B','C');
create view temp_mix_vw as
select decode(lvl,1,col_1,2,col_2,3,col_3) col_n
from temp_mix
,(select level lvl from dual connect by level < 4);
select t1.col_n,t2.col_n,t3.col_n
from temp_mix_vw t1
,temp_mix_vw t2
,temp_mix_vw t3
where t1.col_n != t2.col_n
and t2.col_n != t3.col_n
and t1.col_n != t3.col_n
order by 1,2,3;
COL_N COL_N COL_N
---------- ---------- ----------
A B C
A C B
B A C
B C A
C A B
C B A
I think theres probably a way of doing it using ROLLUP or CUBE, but I've not worked out how yet.
|
|
|
|
| Re: create dynamic groups like; ABC, BCA, CAB, CBA... [message #176953 is a reply to message #176949] |
Mon, 12 June 2006 03:38 |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
OK, maybe I'm missing something here, but grouping by a,b,c,d will give the same results as grouping by d,b,c,a hich returns the same results as grouping by c,d,b,a etc. etc.
SQL> select * from x;
A B C D E
-- -- -- -- ----------
a b c d 20
a b c d 40
a b c d 25
a b c d 50
a b c d 100
b 100
b c d 200
a d 45
m n o p 10
m n x 22
y w 40
11 rows selected.
SQL> select a,b,c,d, sum(e)
2 from x
3 group by a,b,c,d
4 order by a,b,c,d;
A B C D SUM(E)
-- -- -- -- ----------
a b c d 235
a d 45
m n o p 10
m n x 22
b c d 200
b 100
y w 40
7 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select a,b,c,d, sum(e)
2 from x
3 group by d,b,c,a
4* order by a,b,c,d
SQL> /
A B C D SUM(E)
-- -- -- -- ----------
a b c d 235
a d 45
m n o p 10
m n x 22
b c d 200
b 100
y w 40
7 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select a,b,c,d, sum(e)
2 from x
3 group by b,d,c,a
4* order by a,b,c,d
SQL> /
A B C D SUM(E)
-- -- -- -- ----------
a b c d 235
a d 45
m n o p 10
m n x 22
b c d 200
b 100
y w 40
Jim
|
|
|
|
| Re: create dynamic groups like; ABC, BCA, CAB, CBA... [message #177099 is a reply to message #176953] |
Tue, 13 June 2006 01:29 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Just for fun (please don't use it), this works. Just change the ABCDE to whater string of non-repeating letters you want to use:
select perm
from (
SELECT REPLACE ( SYS_CONNECT_BY_PATH ( l1, '|' ), '|' ) perm, len, l2
FROM ( SELECT str, len, rn, SUBSTR ( str, n1, 1 ) l1, SUBSTR ( str, n2, 1 ) l2, n3
FROM ( SELECT str
,len
,rn
, MOD ( rn - 1, len ) + 1 AS n1
, TRUNC ( MOD ( rn - 1, POWER ( len, 2 ) ) / len ) + 1 AS n2
, TRUNC ( ( rn - 1 ) / POWER ( len, 2 ) ) + 1 AS n3
FROM ( SELECT str, len, ROWNUM AS rn
FROM ( SELECT str, LENGTH ( str ) AS len
FROM ( SELECT 'ABCDE' str
FROM DUAL ) )
CONNECT BY LEVEL <= POWER ( len, 3 ) ) ) )
WHERE n3 = len
CONNECT BY l2 = PRIOR l2
AND n3 = PRIOR n3 + 1
START WITH n3 = 1
)
where instr(perm, l2,1, 2) = 0
group by perm, len
having count(*) = len
PERM
------------------------------------------
ABCDE
ABCED
ABDCE
ABDEC
ABECD
ABEDC
ACBDE
ACBED
ACDBE
ACDEB
ACEBD
ACEDB
ADBCE
ADBEC
ADCBE
ADCEB
ADEBC
ADECB
AEBCD
AEBDC
AECBD
AECDB
AEDBC
AEDCB
BACDE
BACED
BADCE
BADEC
BAECD
BAEDC
BCADE
BCAED
BCDAE
BCDEA
BCEAD
BCEDA
BDACE
BDAEC
BDCAE
BDCEA
BDEAC
BDECA
BEACD
BEADC
BECAD
BECDA
BEDAC
BEDCA
CABDE
CABED
CADBE
CADEB
CAEBD
CAEDB
CBADE
CBAED
CBDAE
CBDEA
CBEAD
CBEDA
CDABE
CDAEB
CDBAE
CDBEA
CDEAB
CDEBA
CEABD
CEADB
CEBAD
CEBDA
CEDAB
CEDBA
DABCE
DABEC
DACBE
DACEB
DAEBC
DAECB
DBACE
DBAEC
DBCAE
DBCEA
DBEAC
DBECA
DCABE
DCAEB
DCBAE
DCBEA
DCEAB
DCEBA
DEABC
DEACB
DEBAC
DEBCA
DECAB
DECBA
EABCD
EABDC
EACBD
EACDB
EADBC
EADCB
EBACD
EBADC
EBCAD
EBCDA
EBDAC
EBDCA
ECABD
ECADB
ECBAD
ECBDA
ECDAB
ECDBA
EDABC
EDACB
EDBAC
EDBCA
EDCAB
EDCBA
120 row(s) retrieved
Ross Leishman
|
|
|
|
|
|
|
|
| Re: create dynamic groups like; ABC, BCA, CAB, CBA... [message #177291 is a reply to message #177099] |
Tue, 13 June 2006 21:45 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| rleishman wrote on Tue, 13 June 2006 16:29 |
Just for fun (please don't use it), this works.
|
The reason I said this is because it is neither elegant nor efficient. It will generate n-cubed rows (where n is the number of letters), which it will run through a CONNECT BY performing something in the order of n-squared-to-the-power-of-n un-indexed lookups.
Also, I find it unlikely that the casual reader would be able to work out how the hell it works.
It could be done more efficiently in 10g by exploiting the CONNECT_BY_ISCYCLE psuedocolumn (n-to-the-power-of-n un-indexed lookups).
There are possibly also ways to do it with 10g POWER_MULTISET and POWER_MULTISET_BY_CARDINALITY. These would also be hacks, because these set functions produce Combinations, not Permutations.
The only elegance in my solution is that it does not require programming changes to handle larger character sets. This is pretty academic since it does not scale.
For my money, I would write a relatively simple PIPELINED FUNCTION, and then SELECT ... FROM table(func('STR')).
Ross Leishman
|
|
|
|
| Re: create dynamic groups like; ABC, BCA, CAB, CBA... [message #177506 is a reply to message #177291] |
Thu, 15 June 2006 02:44 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
OK. Here's the 10g version.
I'd actually describe this one as fairly efficient, although not exactly intuitive.
SELECT replace(sys_connect_by_path(let, '|'), '|', '') as perm
FROM (
SELECT str, len, substr(str, ROWNUM, 1) AS let
FROM (
SELECT str, length( str ) AS len
FROM (
SELECT 'ABCDE' str
FROM dual
)
)
CONNECT BY level <= len
)
WHERE level = len
CONNECT BY NOCYCLE let != PRIOR let
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Fri Jan 16 03:23:57 CST 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
