Repeating rows based on a value in a different column

 

Ok, let’s describe a solution step by step.

 

First of all, we’ve to create the “parameters” table (using tempdb):

 

CREATE TABLE #ParameterTable
(
    ID int NOT NULL PRIMARY KEY CLUSTERED
  , Value int NOT NULL
  , RepeatValue int NOT NULL 
);
GO

INSERT INTO #ParameterTable (ID, Value, RepeatValue)
VALUES
    (1, 2, 4)
  , (2, 9, 3)
  , (3, 7, 20);
GO

The table contains only three records with an ID, a simple demo value and a RepeatValue column that contains the number in which we want to split each row in a subset of repeated rows.

The record with ID = 1 will be replicated 4 times

The record with ID = 2 will be replicated 3 times

The record with ID = 3 will be replicated 20 times

 

The proposed solution uses CTEs (Common Table Expression):

 

   (
      SELECT
          ID
        , Value
        , RepeatValue
        , 1
      FROM
        #ParameterTable
      UNION ALL
      SELECT
          ID
        , Value
        , RepeatValue
        , RepeatedTime + 1
      FROM
        ReplicateTable R
      WHERE
        R.RepeatedTime < R.RepeatValue
   )
SELECT
    ID
  , Value
  , RepeatedTime
FROM
    ReplicateTable
ORDER BY
    ID
  , RepeatedTime;
GO

CTE can be used for recursion. In this sample there’s a start member (the first select statement of the WITH definition) and a recursion member (the select statement after the UNION ALL).

The resultset is the following:

 

ID          Value      RepeatedTime
----------- ---------- ------------
1           2          1
1           2          2
1           2          3
1           2          4
2           9          1
2           9          2
2           9          3
3           7          1
3           7          2
3           7          3
3           7          4
3           7          5
3           7          6
3           7          7
3           7          8
3           7          9
3           7          10
3           7          11
3           7          12
3           7          13
3           7          14
3           7          15
3           7          16
3           7          17
3           7          18
3           7          19
3           7          20
27 rows (3 + 4 + 20)

It worked. However there’s an issue. Suppose to have RepeatValue set to 0 (zero) on the record with ID = 3. With the script above the resultset is wrong:

ID          Value      RepeatedTime
----------- ---------- ------------
1           2          1
1           2          2
1           2          3
1           2          4
2           9          1
2           9          2
2           9          3
3           7          1 --> RepeatedTime wrong!!

 

We can exclude the “parameters” with RepeatValue set to 0 (zero) in order to avoid the recursion on those cases. We can add a WHERE condition on the start member of the CTE:

 

WITH ReplicateTable(ID, Value, RepeatValue, RepeatedTime) as
(
      SELECT
          ID
        , Value
        , RepeatValue
        , 1
      FROM
        #ParameterTable
      WHERE
        RepeatValue <> 0
      UNION ALL
      SELECT
          ID
        , Value
        , RepeatValue
        , RepeatedTime + 1
      FROM
        ReplicateTable R
      WHERE
        R.RepeatedTime < R.RepeatValue 
)

The filter will remove the record with ID = 3. Thus, the recursion will not take place for that row.

 

We have to pay attention to the real question. What is the real meaning of the request? What does it means “RepeatValue“? The 0 (zero) value on that column can have different meanings:

– if 0 (zero) exclude from the final resultset (examples above)

– if 0 (zero) show the record as is with RepeatedTime to 0 (zero) –> NOT REPEATED VERSION OF THE RECORD

 

The CTE for the second case is the following:

 

WITH ReplicateTable(ID, Value, RepeatValue, RepeatedTime) as
(
     SELECT

ID
        , Value
        , RepeatValue
        , 0
      FROM
        #ParameterTable
      UNION ALL
      SELECT
          ID
        , Value
        , RepeatValue
        , RepeatedTime + 1
      FROM
        ReplicateTable R
      WHERE
        R.RepeatedTime < R.RepeatValue
)

Now we set the RepeatedTime to 0 (zero) instead of 1:

ID          Value      RepeatedTime
----------- ---------- ------------
1           2          0 --> not repeated version (the main record itself)
1           2          1
1           2          2
1           2          3
1           2          4
2           9          0 --> not repeated version (the main record itself)
2           9          1
2           9          2
2           9          3
3           7          0 --> not repeated version (the main record itself)

 

As we can see the query changes based on the meaning of the request. We can have fun with CTEs but we have to pay attention to performances (in case of thousands of rows they can seriously decrease), recursion levels (for further information about the topic click here) and so on.

Now you can use the script in this post to create your own stored procedure, in order to customize parameters and to apply your logic. Don’t forget to watch for the generated plans!

 

Stay tuned! 🙂