SQL & PL/SQL

Hi

I have a table of colour names, and need to retrieve every conceivable combination of colour groups. As an example, suppose i have the following table:-

WITH colours AS (SELECT 1 id, 'red' colour FROM DUAL
               UNION ALL
               SELECT 2 id, 'blue' colour FROM DUAL
               UNION ALL
               SELECT 3 id, 'green' colour FROM DUAL)
SELECT *
  FROM colours

I would want to retrieve the following results:-

group  id
1       1
2       2
3       3
4       1
4       2
5       1
5       3
6       2
6       3
7       1
7       2
7       3

Could somebody please enlighten me on how i would extract this resultset?

Edited by: tri_harder on Apr 30, 2010 1:42 PM spelling update