SQL Puzzle | Add rows to the data as per the value present in a column

In this puzzle you have to add extra rows to data as per the value present in in the column MultiplyData. If the value in the MultiplyData = 2 then that rows has be added twice including the original row. For more details please check the sample input and expected output.

Sample Input

Id Vals MultiplyData
1 a 1
2 b 2
3 c 3
4 d 4
5 e 2

Expected Output

Id Vals MultiplyData
1 a 1
2 b 2
2 b 2
3 c 3
3 c 3
3 c 3
4 d 4
4 d 4
4 d 4
4 d 4
5 e 2
5 e 2

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE MultiplyDataPuzzle ( Id INT ,Vals VARCHAR(10) ,MultiplyData INT ) GO INSERT INTO MultiplyDataPuzzle VALUES (1,'a',1), (2,'b',2), (3,'c',3), (4,'d',4), (5,'e',2) GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

SOLUTION 1 | USING Generate Number Method

 ```-- ;WITH SingleDigits(Number) AS ( SELECT Number FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (0)) AS X(Number) ) ,Series AS ( SELECT (d1.Number+1) + (10*d2.Number) Number from SingleDigits as d1, SingleDigits as d2 ) SELECT p.* FROM MultiplyDataPuzzle p INNER JOIN Series m ON p.MultiplyData >= m.number ORDER BY p.Id -- ```

Output-1

 ```-- Id Vals MultiplyData ----------- ---------- ------------ 1 a 1 2 b 2 2 b 2 3 c 3 3 c 3 3 c 3 4 d 4 4 d 4 4 d 4 4 d 4 5 e 2 5 e 2 (12 rows affected) -- ```

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com