SQL Server : split one row into many rows (value)

I like using recursive CTEs for this purpose:

with cte as (
      select name, name2, 1 as ind, value
      from mytable
      union all
      select name, name, ind + 1, value
      from cte
      where ind < value
     )
select name, name2, 1
from cte
option (maxrecursion 0);

If your data is not too large, then the performance should be fine. If you have large data, then you might want to consider a numbers table.

You can try the following query

create table #temp (name varchar(20),name2 varchar(20), value int)
insert into #temp values ('aaaa', 'bbbb', 10),('afb', 'bgr', 4)


;WITH
  expanded
AS
(
  SELECT name, name2, value FROM #temp

UNION ALL

  SELECT name, name2, value - 1 FROM expanded WHERE value > 1
)

SELECT
  name, name2, 1 as value,
  ROW_NUMBER() OVER (ORDER BY name, name2, value) AS unique_ref
FROM
  expanded
ORDER BY
  name, 
  name2,
  value

The output is as shown below

name    name2   value   unique_ref
aaaa    bbbb    1       1
aaaa    bbbb    1       2
aaaa    bbbb    1       3
aaaa    bbbb    1       4
aaaa    bbbb    1       5
aaaa    bbbb    1       6
aaaa    bbbb    1       7
aaaa    bbbb    1       8
aaaa    bbbb    1       9
aaaa    bbbb    1       10
afb     bgr     1       11
afb     bgr     1       12
afb     bgr     1       13
afb     bgr     1       14

You can find the demo here - Demo