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