Duplicate a Row Based on a Condition SQL

Question

I have a table that looks like this

+-------+------+------+----------+ 
| Index |  F1  |  F2  | Multiply | 
+-------+------+------+----------+
|  0    |  LG  |  SC  |     1    |
|  1    |  WE  |  CC  |     1    |
|  2    |  TY  |  PL  |     2    |
+-------+------+------+----------+

And I want to multiply the 'Multiply' Column by 2 to determine how many rows to add to the result. With the example above, I want my SQL Query to return:

+------+-----+-----+
|Index | F1  | F2  |
+------+-----+-----+
|  0   |  LG | SC  | 
|  0   |  LG | SC  |
|  1   |  WE | CC  |
|  1   |  WE | CC  |
|  2   |  TY | PL  |
|  2   |  TY | PL  |
|  2   |  TY | PL  |
|  2   |  TY | PL  |
+------+-----+-----+

I don't have much experience writing SQL queries so help would be much appreciated.

uzi · Answer 1 · 2018-02-01T09:20:27.730

7

Try this

with myTable as (
    select * from (values 
        (0,'LG','SC',1)
        ,(1,'WE','CC',1)
        ,(2,'TY','PL',2)
    ) t ([Index], F1, F2, Mutiply)
)
select
    [Index], F1, F2
from
    myTable m
    join master.dbo.spt_values v on m.Mutiply * 2 > v.number
where
    Type = 'P'

Edit: adding some description

By writing a query like

select * from table 
cross join (values (1),(2)) t(n)

we can double rows in the table. And cross join (values (1),(2),(3),(4)) t(n) will quadruple the rows. In this case row multiplication will depend on column Multiply. So we need to multiply by 2 value of column Multiply and join with table with sequence of numbers (0,1,2,3,4,5,6,7...) where number is less than calculated value. For example, for Multiply = 2, join condition will be 4 numbers (0, 1, 2, 3) which are less than 2*2. And those 4 numbers will quadruple initial row.

master.dbo.spt_values is only used to get numbers sequence. Sequence can be generated at runtime or another table with numbers sequence can be used

edited Feb 01 '18 at 09:20

answered Feb 01 '18 at 07:56

uzi

4,118
1
15
22

2

Can you please add some explanation to this answer. It will be of great help. – PSK Feb 01 '18 at 08:04
1

Added a description as best as I could :) Let me know if it is understandable – uzi Feb 01 '18 at 09:21
@uzi Perfect :) – PSK Feb 01 '18 at 10:17
Excellent .. But why did you keep a condition `> v.number` .didn't get here – Mahesh.K Feb 01 '18 at 10:29

gofr1 · Answer 2 · 2018-02-02T14:11:28.810

Another way is to use recursive CTE:

;WITH cte AS (
    SELECT *
    FROM (VALUES
    (0, 'LG', 'SC', 1),
    (1, 'WE', 'CC', 1),
    (2, 'TY', 'PL', 2)
    ) as t([Index], F1, F2, Multiply)
), final AS (
    SELECT  [Index], 
            F1, 
            F2, 
            Multiply*2 as Multiply 
    FROM cte  -- At first we get basic recordset with x2 in Multiply
    UNION ALL 
    SELECT  [Index], 
            F1, 
            F2, 
            Multiply-1
    FROM final  -- On each iteration we select columns we need and
    WHERE Multiply-1 > 0   -- use Multiply as counter
)

SELECT  [Index], 
        F1, 
        F2
FROM final
ORDER by [Index]

Output:

Index       F1   F2
----------- ---- ----
0           LG   SC
0           LG   SC
1           WE   CC
1           WE   CC
2           TY   PL
2           TY   PL
2           TY   PL
2           TY   PL

(8 rows affected)

Duplicate a Row Based on a Condition SQL

2 Answers2

Linked