T-SQL: Splitting one row into many

12 January 2011 | Eric Swanson | Development, Microsoft | Tags: , , , , , , ,

Something for your SQL toolbox…

I was recently challenged with needing to split a row into multiple rows (a quantity across multiple dates).

We were using a CURSOR, but the performance was horrible. When I converted it to a recursive CTE it went from 6+ minutes to a few seconds!

Below was my “theorem proof” before I implemented the final solution…

*Note: PIVOT would be used to split a row into multiple columns rather than rows.

Links:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

DECLARE @RowsToSplit TABLE (
      Id INT IDENTITY(1, 1) NOT NULL
      -- how many rows are desired after the split?
      , MaxRows INT NOT NULL
      -- what value to split across the rows?
      , QuantityToSplit INT NOT NULL
);
 
INSERT INTO @RowsToSplit VALUES ( 20, 100 );
INSERT INTO @RowsToSplit VALUES ( 5, 15 );
 
-- Recursive Common Table Expression (CTE)
WITH SplitRowsCTE ( Id, MaxRows, QuantityToSplit, Quantity, RowIndex )
AS (
      -- "Anchor" Set: The root set of the recursive function.
      SELECT 
            Id -- row tracking
            , MaxRows -- remember the goal # of rows
            , QuantityToSplit -- remember the goal value to split
            -- split the first row's quantity
            , QuantityToSplit / MaxRows AS Quantity
            -- start at index 1
            , 1 AS RowIndex
      FROM @RowsToSplit -- anchor source
      -- Recursive Set: Iterate from the source until all rows have been generated.
      UNION ALL
      SELECT
            Id -- row tracking
            , MaxRows -- remember the goal # of rows
            , QuantityToSplit -- remember the goal value to split
            -- split the row's quantity
            , QuantityToSplit / MaxRows AS Quantity
            -- increment the row index
            , RowIndex + 1 AS RowIndex
      FROM SplitRowsCTE
      -- stop just before we get to the max rows (root has already been created)
      WHERE RowIndex < MaxRows
)
 
-- Call the recursive CTE function
-- Eliminate the source information from the result set
SELECT Id, RowIndex, Quantity
FROM SplitRowsCTE
ORDER BY Id, RowIndex;

Leave a Reply