Repeating rows based on a value in a different column

I’ve just replied to a question in dotnethell italian forum about the topic in the title. Actually it’s not the first time I got this kind of question:

“How can I repeat a row using a value that is stored in another column of the same table?”

 
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):
 
WITH ReplicateTable(ID, Value, RepeatValue, RepeatedTime) as
   (
      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! 🙂
 
 

2 thoughts on “Repeating rows based on a value in a different column

  1. — an interesting blog, and a slightly odd problem!
    — If you have a number table in your database, you can get this result simply by doing this

    Select ID, Value, number
    from #ParameterTable inner join #Numbers
    on RepeatValue>=number

    /* if you haven’t, then this will do the trick just as well.(I’m assuming that you don’t need to repeat more than a hundred times. */

    Select ID, Value, number
    from #ParameterTable inner join
    (select tens.number+units.number+1
    from (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) AS tens (number)
    cross join
    (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS units (number)) as numbers(number)
    on RepeatValue>=number

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s