This content has been marked as final. Show 6 replies
I hope (and a little sure) that there is some better way than this...
Please try this -
with xx as( select 'x' col from dual UNION ALL select 'y' col from dual UNION ALL select 'z' col from dual ), x1 as ( select col from xx UNION select col from ( select a.col||b.col col from xx a, xx b ) where substr(col,1,1) != substr(col,2,1) ) select col from ( select col, rank() over(partition by substr(col,1,1) order by col) rn from x1 ) where rn !=3 UNION select c.col||d.col from ( select col, rank() over(partition by substr(col,1,1) order by col) rn from x1) c, xx d where rn <3 and d.col != substr(c.col,1,1) and d.col != substr(c.col,2,1);
I'll try for something efficient,,,
x xy xyz y yx yxz z zx zxy
Edited by: ranit B on Jan 2, 2013 4:17 PM
-- code modified...
Please check the below query,
SELECT T.COL FROM TEMP AS T
SELECT T.COL||T1.COL FROM temp AS T JOIN TEMP AS T1 ON T.COL <> T1.COL
SELECT T.COL||T1.COL||T2.COL FROM temp AS T JOIN TEMP AS T1 ON T.COL <> T1.COL JOIN temp AS T2 ON T1.COL <> T2.COL
I did not get the results what you have expected. But I got all combination of data.
Edited by: AravindhK on Jan 2, 2013 4:28 PM
Bilal wrote:'XYZ', 'YXZ' and 'ZXY' are all the same combination , but they are different permutations . It looks like you're interested in permutations, not combinations.
I have the following data in col column of table t:
I want to retrieve all the possible combinations of these values like:
Why do you want the permutations above, but not others, such as 'XZ' or 'ZYX'?
To get all the permutations:
SELECT REPLACE ( SYS_CONNECT_BY_PATH (col, ',') , ',' ) AS permutation FROM t CONNECT BY NOCYCLE col != PRIOR col;
To get all combinations, just use > instead of != in the CONNECT BY clause:
X XY XYZ XZ XZY Y YX YXZ YZ YZX Z ZX ZXY ZY ZYX
SELECT REPLACE ( SYS_CONNECT_BY_PATH (col, ',') , ',' ) AS combination FROM t CONNECT BY NOCYCLE col > PRIOR col;
One problem with joining is that you need to hard-code a separate copy of the table for each possible value. If you have 20 distinct values of col, then you need to join 20 copies of the table. CONNECT BY works with any number of values (0 or more).
X XY XYZ XZ Y YZ Z
Edited by: Frank Kulash on Jan 2, 2013 7:15 AM
Added query for combinations