Duplicate a Row Based on a Condition SQL

问题:

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.

回答1:

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

回答2:

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)