with mydata as (
select 'A' mycol from dual
union all
select 'B' mycol from dual
union all
select 'C' mycol from dual
union all
select 'D' mycol from dual
union all
select 'E' mycol from dual
union all
select 'F' mycol from dual
union all
select 'G' mycol from dual
union all
select 'H' mycol from dual
)
select c1.mycol, c2.mycol
from mydata c1
cross join mydata c2
where c1.mycol < c2.mycol
order by c1.mycol, c2.mycol
/
declare @categories table
(
ID nvarchar(max)
, CatCode nvarchar(max)
)
insert into @categories
values ('01', 'A'), ('02', 'B'), ('03', 'C'), ('04', 'D'), ('05', 'E'), ('06', 'F'), ('07', 'G'), ('08', 'H');
Declare @counter int
Select @counter = COUNT(*)
from @categories;
With Permutations (permutation, IDs, Depth)
as
(
Select c.CatCode
, c.ID + ';'
, Depth = 1
From @categories c
union all
Select permutation + c.CatCode
, IDs + ID + ';'
, Depth = Depth + 1
from @categories as c
, Permutations as p
Where p.Depth < @counter
AND IDs not like '%' + ID + ';%'
)
Select *
from Permutations
where Depth = @counter
order by permutation
select t1.cat, '' cat
from T t1
union
select t2.cat, t3.cat
from T t2, T t3
where t2.cat <> t3.cat
This would give you 64 rows.CREATE OR REPLACE TYPE vcarray AS TABLE OF VARCHAR2(4000);
WITH yourdata
AS ( SELECT SUBSTR('ABCDEFGH', LEVEL, 1) cat
FROM DUAL
CONNECT BY LEVEL <= LENGTH('ABCDEFGH'))
SELECT (SELECT LISTAGG(COLUMN_VALUE, ',') WITHIN GROUP (ORDER BY COLUMN_VALUE)
FROM TABLE(x.COLUMN_VALUE))
sets
FROM TABLE(POWERMULTISET((SELECT CAST(COLLECT(cat) AS vcarray) FROM yourdata))) x
ORDER BY LENGTH(sets), sets;
WITH yourdata
AS ( SELECT SUBSTR('ABCDEFGH', LEVEL, 1) cat
FROM DUAL
CONNECT BY LEVEL <= LENGTH('ABCDEFGH'))
SELECT (SELECT LISTAGG(COLUMN_VALUE, ',') WITHIN GROUP (ORDER BY COLUMN_VALUE)
FROM TABLE(x.COLUMN_VALUE))
sets
FROM TABLE(POWERMULTISET((SELECT CAST(COLLECT(cat) AS ora_mining_varchar2_nt) FROM yourdata))) x
ORDER BY LENGTH(sets), sets
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
How to share SSIS Package? | 6 | 37 | |
stored procedures times out after 2 hours in sql server 2014 | 16 | 38 | |
SQL Improvement ( Speed) | 14 | 28 | |
SQL Workhours Count beetween Workhours | 3 | 17 |
Join the community of 500,000 technology professionals and ask your questions.