Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
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 |
---|---|---|---|
error in oracle form | 11 | 28 | |
SQL Distinct Question | 3 | 15 | |
SQL Server Extended Events: list of deadlocks growing too long - how to cleanup? | 21 | 25 | |
TSQL: return only records which have only one type of value. | 1 | 16 |
Join the community of 500,000 technology professionals and ask your questions.