sql-servercross-apply

How can I use Cross Apply to multiple rows?


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
)

enter image description here

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.

enter image description here

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))


Solution

  • ;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