oracle sql query to combine field percentages

Question

I have a table such as this

NAME   CATEGORY    PERCENT
Black  Color       0.10
Blue   Color       0.30
Green  Color       0.60
Fast   Speed       0.40
Slow   Speed       0.60

What I want the output to be is

COMBINEDCAT   COMBINEDPC
BlackFast      0.04
BlackSlow      0.06
BlueFast       0.12
BlueSlow       0.18
GreenFast      0.24
GreenSlow      0.36

So essentially I'm looking for a way to multiply the names against eachother to form all possible category outcomes, does this make any sense? I've been struggling with this for some time, any help is appreciated!

Thanks!

EDIT: There could be limitless categories, so I'm looking for something that would not need to reference each category in the query.

Answer 1

select t1.Name || t2.Name as CombinedCat,
       t1.Percent * t2.Percent as CombinedPc
  from your_table t1
  join your_table t2
    on t2.Category = 'Speed'
 where t1.Category = 'Color'
 order by CombinedCat

EDIT: Adjustment in the problem description

If you're looking to do this with a dynamic amount of categories, you can do it with the following query, which uses a recursive CTE:

with Categories as (
  select category,
         row_number() over (order by category) as seq
    from your_table
   group by category),
RecursiveCTE (Name, Percent, seq) as (
  select t.Name,
         t.Percent,
         c.seq
    from your_table t
    join Categories c
      on c.category = t.category
     and c.seq = 1
   union all   
  select r.Name || t.Name as Name,
         r.Percent * t.Percent as Percent,
         c.seq
    from RecursiveCTE r
    join Categories c
      on c.seq = r.seq + 1
    join your_table t
      on t.category = c.category
)
select t.Name as CombinedCat,
       Percent as CombinedPc
  from RecursiveCTE t
 where t.seq = (select max(seq) from Categories)
order by t.Name

SQLFiddle Demo

The query above concatenates the category names alphabetically, but you can adjust that by changing the order by clause in the row_number() function in the Categories CTE:

row_number() over (order by category) as seq