Generate rows based on the values of a column

Recently while working on a module, I was asked to create data rows based on the column values of a SQL table. I needed to generate the rows in table B based on the values of column table A.

So we had a  SQL table with UserId and Quantity as column and below possible values.

UserId Quantity
ACC_xxx_003 1
ACC_xxx_004 4
ACC_xxx_005 1
ACC_xxx_006 3

The desired output was:

UserId Quantity
ACC_xxx_003 ACC_xxx_003-001
ACC_xxx_004 ACC_xxx_004-004
ACC_xxx_004 ACC_xxx_004-004
ACC_xxx_004 ACC_xxx_004-004
ACC_xxx_004 ACC_xxx_004-004
ACC_xxx_005 ACC_xxx_005-001
ACC_xxx_006 ACC_xxx_006-003
ACC_xxx_006 ACC_xxx_006-003
ACC_xxx_006 ACC_xxx_006-003

To get this working there were different approaches but I chose to use CTE for its simplicity and ease of use. Below is the code which I created.

CREATE TABLE UserMapping (
	UserId VARCHAR(50)
	,Quantity INT
	)
 
INSERT INTO UserMapping
VALUES 
	('ACC_xxx_003',1)
	,('ACC_xxx_004',4)
	,('ACC_xxx_005',1)
	,('ACC_xxx_006',3);
 
	--===== Create number table on-the-fly
WITH CTE (n)
AS (
	SELECT 1 AS n
	
	UNION ALL
	
	SELECT n + 1 AS n
	FROM CTE
	WHERE n < 101
	)
	,Num2 (n)
AS (
	SELECT 1
	FROM CTE AS X
		,CTE AS Y
	)
	,Nums (n)
AS (
	SELECT ROW_NUMBER() OVER (
			ORDER BY n
			)
	FROM Num2
	)
SELECT UserId
	,Quantity AS Serial
INTO FormattedTable
FROM UserMapping
CROSS APPLY (
	SELECT n
	FROM Nums
	) d
WHERE Quantity >= n
 
SELECT UserId
,UserId + '-' + right('000' + cast(Serial AS VARCHAR(50)), 3) AS Serial
FROM FormattedTable
ORDER BY UserId
 
DROP TABLE UserMapping
	,FormattedTable
Advertisements