Generate data from 1 to specific value

Question

I am going to generate a result like this suppose I have this records

Salah 3
John  2

I will expect this

Salah
Salah
Salah
John
John

Answer 1

One method, if you always have a small value for n is to use a rCTE:

WITH rCTE AS(
    SELECT [Name],
           N,
           1 AS I
    FROM dbo.YourTable
    UNION ALL
    SELECT [Name],
           N,
           I + 1
    FROM rCTE
    WHERE I < N)
SELECT [Name]
FROM rCTe
ORDER BY [Name] DESC;

If you have much larger numbers, use a more performamt Tally. I use an inline here:

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT --TOP (SELECT MAX(N) FROM dbo.YourTable) --Limits the number of rows, which could also provide a performance benefit if you only sometimes have large numbers
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3) --1000 rows, add more cross joins for more rows
SELECT [Name]
FROM dbo.YourTable YT
     JOIN Tally T ON YT.N >= T.I
ORDER BY YT.[Name] DESC;

db<>fiddle

Answer 2

Having a permanent table of all positive integers (or at least as many as you realistically need) would let you have a very simple query, like:

select
    p.[Name]
from
    @People p
    inner join numbers on p.[Count] >= numbers.[Count]
order by
    p.[Name]
    ;

But if you don't want or can't have a table like that, you could use an approach like the one suggested here to generate such a list on the fly, and use it like this:

declare @People table
(
    [Name] varchar(15),
    [Count] int
);

insert into @People
values
('Salah', 3),
('John', 2);

WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))

select
    p.[Name]
from
    @People p
    inner join
    (
        SELECT [Count] = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM x ones, x tens, x hundreds, x thousands
    ) numbers on p.[Count] >= numbers.[Count]
order by
    p.[Name]
    ;