Oracle sql to combine fields as a percentage

Question

I have a table like

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

I want the output to be

 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 names against eachother to form all possible category results, does that make sense? I struggled with this for some time, any help is appreciated!

Thanks!

EDIT: There can be unlimited categories, so I'm looking for something that will not need to refer to 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: setting in problem description

If you want to do this with a dynamic number of categories, you can do it with the following query that 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 above query combines the category names in alphabetical order, but you can customize this by changing the order by clause in the row_number() function in Categories CTE:

 row_number() over (order by category) as seq 

Answer 2

How about this one.

• You join all other categories "<>" than you.

• But join only those who are "bigger" than you to avoid duplication of duplicates.

.

  SELECT T1."CATEGORY", T2."CATEGORY", T1."NAME"||T2."NAME" as "COMBINEDCAT", T1."PERCENT"*T2."PERCENT" as "COMBINEDPC" FROM Table1 T1 INNER JOIN Table1 T2 ON T1."CATEGORY" < T2."CATEGORY" ORDER BY T1."CATEGORY", T2."CATEGORY", T1."NAME"||T2."NAME" 

SQL Fiddle Demo

I include categories in select so you can check the connections, also include a new COST category

OUTPUT

 | CATEGORY | CATEGORY | COMBINEDCAT | COMBINEDPC | |----------|----------|-------------|------------| | Color | Cost | BlackCheap | 0.03 | | Color | Cost | BlackHigh | 0.04 | | Color | Cost | BlackLow | 0.03 | | Color | Cost | BlueCheap | 0.09 | | Color | Cost | BlueHigh | 0.12 | | Color | Cost | BlueLow | 0.09 | | Color | Cost | GreenCheap | 0.18 | | Color | Cost | GreenHigh | 0.24 | | Color | Cost | GreenLow | 0.18 | | Color | Speed | BlackFast | 0.04 | | Color | Speed | BlackSlow | 0.06 | | Color | Speed | BlueFast | 0.12 | | Color | Speed | BlueSlow | 0.18 | | Color | Speed | GreenFast | 0.24 | | Color | Speed | GreenSlow | 0.36 | | Cost | Speed | CheapFast | 0.12 | | Cost | Speed | CheapSlow | 0.18 | | Cost | Speed | HighFast | 0.16 | | Cost | Speed | HighSlow | 0.24 | | Cost | Speed | LowFast | 0.12 | | Cost | Speed | LowSlow | 0.18 | 

Answer 3

You can cross join to get the desired result.

Sample Data Script

 select n.name||s.name as combinedcat, n.percent*s.percent from (select distinct name, percent from tablename where category = 'Color') n cross join (select distinct name, percent from tablename where category = 'Speed') s