Split 1 row into 2 rows with % of total

create table #testdata
(
    ID int,
    Total money
)

insert into #testdata
(
    ID,
    Total
)
select
    1,
    100
union all
select
    2,
    105
union all
select
    3,
    110
union all
select
    4,
    150


select * from #testdata

How can I convert each row into 2 rows, the first row having 75% of the total, and the other row being 25% of the total where I can also then include other columns?

Answer

Create a CTE with the 2 percentages and cross join it to the table:

with cte as (select 0.75 val union all select 0.25)
select t.ID, c.val * t.Total result 
from cte c cross join #testdata t
order by t.ID, c.val desc

See the demo.
Results:

> ID |  result
> -: | ---------:
>  1 |  75.00
>  1 |  25.00
>  2 |  78.75
>  2 |  26.25
>  3 |  82.50
>  3 |  27.50
>  4 | 112.50
>  4 |  37.50