Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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