is there a way to duplicate a row?

Question

I want for something that works the opposite way that a COUNT, that's like a inverse GROUP BY (a SPLIT BY?) but that you can work with more freely.

So far I've seen all "commands" are for restrictions or specification, meaning that you can't create a bigger table/set that the one you already have. I am talking about creating some sort of dynamic construction of sets.

Any way to, for instance, create a power set or an "exponential" cross join' (each row joined with size_in_rows copies of himself)? Or some sort of recursion?

An example of what I would need:

I have a table with a couple of fields, each one contains info and a "count" integer value. I need to SELECT __ and send that many "count" copies of the same exact row, to read it one by one with another program outside of the database.

Answer 1

Here's an approach for SQL Server: Fiddle Example

declare @rowToBeCopiedId bigint = 1
, @count int = 10

; with noRowsToInsertCte
(
    select 1 x
    where @count > 0
    union all
    select x + 1
    from noRowsToInsertCte 
    where @count > x
)
insert MyTable (col1, col2)
select col1, col2
from MyTable
cross join noRowsToInsertCte
where id = @rowToBeCopiedId

If instead of duplicating records in the database you just want to duplicate the record in the result set, you can do it as below. This example uses a column in the data to say how many times the related record should be repeated, and uses a view to allow you to easily reuse this logic:

create view vMyTableWithCount as
with innerCte as 
(
    select id
    , col1
    , col2
    , cnt
    , 1 i
    from MyTable
    where cnt > 0

    union all

    select id
    , col1
    , col2
    , cnt
    , i + 1
    from innerCte
    where i < cnt
) 
select *
from innerCte

For more context, please see the fiddle example.