Create multiple records from 1 record with the given number of records SQL

Transact-SQL
1

Hi Everybody,

I have a table with the following record

  Doc No              Name      Quantity   Generate_Number
 ------------------------------------------------------------------------
 TM220101             Tomy         10               4

How can I create 4 records (Generate_Number)

  Doc No              Name   Quantity   
 -----------------------------------------------
 TM220101-1           Tomy      10          
 TM220101-2           Tomy      10          
 TM220101-3           Tomy      10          
 TM220101-4           Tomy      10

Thanks so much.

2

I would advise to create a Tally table with a CTE. It is usefull in many situations. This example will work as long as the Generate_number is less then 1000.

;WITH Tally (n) AS
(
    /* 1000 rows */
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
), YourTable AS
(
 SELECT
	'TM220101' AS DocNo,
	'Tomy' AS [Name],
	10 AS Quantity,
	4 AS GenerateNumber
)
SELECT 
	Tally.n,
	YourTable.DocNo,
	CONCAT(YourTable.DocNo,'-', Tally.n) AS NewDocNo,
	YourTable.[Name],
	YourTable.Quantity
FROM Tally 
	INNER JOIN YourTable 
		ON Tally.n <= YourTable.GenerateNumber;
1 Like
3

Thanks so much.

4

hi

hope this helps

another way to do this .. using recursive cte

create data script

drop table temp

create table temp ( DocNo varchar(20),Name varchar(10),Quantity int, Generate_Number int )

insert into temp select 'TM220101', 'Tomy', 10 , 4

select * from temp

 ; with rec_cte as 
 (
 select DocNo  as DocNo  ,name , quantity , 1 as cnt from temp
 union all 
 select b.DocNo, name, quantity,cnt+1   from  rec_cte b where b.cnt+1 <= (select generate_number from temp)
 ) 
 select DocNo+'-'+cast(cnt as varchar) , name , quantity  from rec_cte

image

5

Thank you.