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