how determine the best path that satisfies a condition declare @flat table
(
    id int not null,
    percentage int not null,
    value float not null
)

insert into @flat (id, percentage, value)
select 1,10,0.333
union all
select 1,20,0.4
union all
select 2, 50, 0.5
union all
select 2, 70, 0.67
union all
select 3, 10, 0.125
union all
select 3, 40, 0.325


having this sample data, how could i determine the rows that maximize the sum of the values ?
Having the following conditions:


one percentage per id only  
the sum of the percentages must be 100


there is two possible solutions:

-- id percentage  value     sum
--  1     10      0.333    0.333  
--  2     50      0.5      0.833 
--  3     40      0.325    1.158

-- id percentage  value     sum
--  1     20      0.4       0.4  
--  2     70      0.67      1.070 
--  3     10      0.125     1.195


but it matters only the last one.
Congratulations, you have a subset sum problem! It's an NP-complete problem. Arriving at the solution is going to call for brute force. i.e. Generate and test all possible subsets. WARNING: This may involve the sketchy Cartesian products your mother warned you about.

Depending on the size of your actual data domain, in SQL that is going to range from "computationally expensive but it works" at best to "who jammed the kernel to 100% and crashed the database server?" at worst.

As a computationally-intensive problem, it is more advisable to solve it in a dynamic programming language than in a SQL DBMS.