How can I use Cross Apply to multiple rows?

Question

I have this table :

;WITH cte AS (
    SELECT Name='john' , Times=1    
    UNION ALL
    SELECT 'paul' ,2
    UNION ALL
    SELECT 'george' , 3
    UNION ALL
    SELECT 'ringo' , 1
)

I want to display each row , Times times :

John 1
Paul 2
Paul 2
george 3
george 3
george 3
ringo 1

So I know that if I write Cross apply like :

SELECT *
FROM   cte
       CROSS APPLY(
        SELECT 1 AS ca
        UNION 
        SELECT 2
       ) y

Then each row will be displayed 2 times.

but I don't want 2 times. I want Times times

Question

How can I enhance my query to do it ?

nb :

a non-intelligent solution that came to my mind is to create a udf which create Times rows for n parameter - and then in the Cross Apply I simply do : select * from udf_toTable(Times))

Answer 1

To use with a CROSS APPLY you can create a table-valued function like this:

CREATE FUNCTION FN_MULTIPLY_ROW (@ROWS INT) RETURNS TABLE AS
RETURN
WITH A AS (
        SELECT ROW_LOOP = 1
    UNION ALL
        SELECT ROW_LOOP + 1
        FROM A
        WHERE ROW_LOOP < @ROWS
    )
SELECT ROW_LOOP FROM A

And just simple use the cross apply:

;WITH cte AS (
    SELECT Name='john' , Times=1    
    UNION ALL
    SELECT 'paul' ,2
    UNION ALL
    SELECT 'george' , 3
    UNION ALL
    SELECT 'ringo' , 1
)
SELECT * --> You have the "ROW_LOOP" here if you want to know the number exactly
FROM CTE
CROSS APPLY FN_MULTIPLY_ROW(TIMES)

Plus: If the loop is bigger than 100 you will need to add the MAXRECURSION option, use wisely

...
CROSS APPLY FN_MULTIPLY_ROW(TIMES)
OPTION (MAXRECURSION 0)

Answer 2

I've managed to do this : ( still having sad face becuase of sys.objects)

;WITH cte AS (
    SELECT Name='john' , Times=1    
    UNION ALL
    SELECT 'paul' ,2
    UNION ALL
    SELECT 'george' , 3
    UNION ALL
    SELECT 'ringo' , 1
)  

SELECT *
FROM   cte
       CROSS APPLY(
                select top (cte.Times) 'bla'=1 from sys.objects
       ) y

update , after looking at the answers : this is a solution which uses them with CROSS APPLY :

;WITH cte AS (
    SELECT Name='john' , Times=1    
    UNION ALL
    SELECT 'paul' ,2
    UNION ALL
    SELECT 'george' , 3
    UNION ALL
    SELECT 'ringo' , 1
)   ,  times
AS
(
  select 1 n, MAX(cte.Times) Times
  from cte
  union all
  select t.n+1, t.Times
  from times t
  where t.n+1<=t.Times
)

SELECT *
FROM   cte
       CROSS APPLY(  
                select top (cte.Times) n from times
       ) y

Answer 3

SELECT A.Name, A.Times
FROM (VALUES
    ('John', 1)
  , ('Paul', 2)
  , ('George', 3)
  , ('Ringo', 1)
) A (Name, Times)
CROSS APPLY(VALUES
    (1), (2), (3)
) B (n)
WHERE A.Times >= B.n
ORDER BY A.Name;

Only downside I can think of to this is that you have to put the numbers in manually but that can easily be solved with a Numbers Table/TVF:

SELECT A.Name, A.Times
FROM (VALUES
    ('John', 1)
  , ('Paul', 2)
  , ('George', 3)
  , ('Ringo', 1)
) A (Name, Times)
CROSS APPLY dbo.RangeSmallInt(1, A.Times) B;

I also noticed in the comments that you ran into the limit of sys.objects and someone posted a wonderful link to generating sequences. The RangeSmallInt function used in my example is based off of that post and is very performant. Here's the code:

-- Generate a range of up to 65,536 contiguous BIGINTS
CREATE FUNCTION dbo.RangeSmallInt (
    @n1 BIGINT = NULL
  , @n2 BIGINT = NULL
)
RETURNS TABLE
AS
RETURN (
    WITH Numbers AS (
        SELECT N FROM(VALUES
            (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 16
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 32
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 48
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 64
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 80
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 96
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 112
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 128
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 144
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 160
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 176
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 192
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 208
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 224
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 240
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 256
        ) V (N)
    )    
    SELECT TOP (
               CASE
                   WHEN @n1 IS NOT NULL AND @n2 IS NOT NULL THEN ABS(@n2 - @n1) + 1
                   ELSE 0
               END
           )
           N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 + CASE WHEN @n1 <= @n2 THEN @n1 ELSE @n2 END
    FROM Numbers A, Numbers B
    WHERE ABS(@n2 - @n1) + 1 < 65537
);

Extending it to support of size INT:

-- Generate a range of up to 4,294,967,296 contiguous BIGINTS
CREATE FUNCTION dbo.RangeInt (
    @num1 BIGINT = NULL
  , @num2 BIGINT = NULL
)
RETURNS TABLE
AS
RETURN (
    WITH Numbers(N) AS (
        SELECT N
        FROM dbo.RangeSmallInt(0, 65535)
    )
    SELECT TOP (
               CASE
                   WHEN @num1 IS NOT NULL AND @num2 IS NOT NULL THEN ABS(@num1 - @num2) + 1
                   ELSE 0
               END
           )
           N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + CASE WHEN @num1 <= @num2 THEN @num1 ELSE @num2 END - 1
    FROM Numbers A
       , Numbers B 
    WHERE ABS(@num1 - @num2) + 1 < 4294967297
);

Answer 4

;WITH cte AS (
    SELECT Name='john' , Times=1    
    UNION ALL
    SELECT 'paul' , Times=2
    UNION ALL
    SELECT 'george' , Times=3
    UNION ALL
    SELECT 'ringo' , Times=1
),
multi as
(
    select 
        Name, Times, Times as num
    from cte
    union all
    select 
        Name, Times, num - 1
    from multi 
    where num > 1
)
select Name, Times from multi
order by Name

UPDATE

without recursion

;WITH cte AS (
    SELECT Name='john' , Times=1    
    UNION ALL
    SELECT 'paul' , Times=2
    UNION ALL
    SELECT 'george' , Times=3
    UNION ALL
    SELECT 'ringo' , Times=1
)
select cte.*
from cte join 
    -- generate sequence of numbers 1,2 ... MAX(Times)
    (select top (select MAX(Times) from cte) ROW_NUMBER() over (order by object_id) rowNum from sys.objects) t
on cte.Times >= t.rowNum 
order by name

Answer 5

You don't need to use cross apply. Use rather a recursive CTE:

;WITH cte AS (
    SELECT Name='john' , Times=1    
    UNION ALL
    SELECT 'paul' , Times=2
    UNION ALL
    SELECT 'george' , Times=3
    UNION ALL
    SELECT 'ringo' , Times=1
)
, res as (
select Name, 1 RowNum
from cte
union all
select cte.Name, res.RowNum+1
from cte
  join res on cte.Name=res.Name
where res.RowNum+1<=cte.Times
)
select res.*, cte.Times
from res
  join cte on cte.Name=res.Name
order by 1, 2

UPDATE Another dynamic max.

;WITH cte AS (
    SELECT Name='john' , Times=1    
    UNION ALL
    SELECT 'paul' , Times=2
    UNION ALL
    SELECT 'george' , Times=3
    UNION ALL
    SELECT 'ringo' , Times=1
), times
AS
(
  select 1 n, MAX(cte.Times) Times
  from cte
  union all
  select t.n+1, t.Times
  from times t
  where t.n+1<=t.Times
)
SELECT 
  c.*
FROM CTE AS c
INNER JOIN times AS t ON c.Times >= t.n
order by 1, 2