Separate rows from the table using the CTE

advertisements

I have the following rows from a table in SQL Server 2008 R2

+-------------------------------------------+
|  ID          EntryType dt         price   |
+-------------------------------------------+
| 14          4         2012-11-07 0.025000 |
| 16          5         2012-11-07 0.026000 |
| 18          6         2012-11-07 0.026000 |
| 20          7         2012-11-07 0.026000 |
+-------------------------------------------+

What I would like to do is spread the rows based on the EntryType (the EntryType doesn't change)

For EntryType = 4 (1 row)
For EntryType = 5 (2 row)
For EntryType = 6 (3 row)
For EntryType = 7 (9 row)

and the dt field will be incremented (in a Month interval), so the output looks like this:

+-----------+-----------+-------+
| EntryType |    dt     | Price |
+-----------+-----------+-------+
|         4 | 11/7/2012 | 0.024 |
|         5 | 12/7/2012 | 0.025 |
|         5 | 1/7/2013  | 0.025 |
|         6 | 2/7/2013  | 0.026 |
|         6 | 3/7/2013  | 0.026 |
|         6 | 4/7/2013  | 0.026 |
|         7 | 5/7/2013  | 0.027 |
|         7 | 6/7/2013  | 0.027 |
|         7 | 7/7/2013  | 0.027 |
|         7 | 8/7/2013  | 0.027 |
|         7 | 9/7/2013  | 0.027 |
|         7 | 10/7/2013 | 0.027 |
|         7 | 11/7/2013 | 0.027 |
|         7 | 12/7/2013 | 0.027 |
|         7 | 1/7/2014  | 0.027 |
+-----------+-----------+-------+

Is it possible to do that with CTE and SQL?


Here is a way to do this in a recursive CTE:

;with RecordCounts as (
    -- Establish row counts for each EntryType
    select 4 as EntryType, 1 as RecordCount
    union all select 5, 2
    union all select 6, 3
    union all select 7, 9
), PricesCte as (
    -- Get initial set of records
    select ID, p.EntryType, (select min(dt) from MyTable) as dt, price, 1 as RecordNum
    from MyTable p
        join RecordCounts c on p.EntryType = c.EntryType -- Only get rows where we've established a RecordCount
    -- Add records recursively according to RecordCount
    union all
    select ID, p.EntryType, dt, price, RecordNum + 1
    from PricesCte p
        join RecordCounts c on p.EntryType = c.EntryType
    where RecordNum + 1 <= c.RecordCount
)
select EntryType,
    dateadd(mm, row_number() over (order by EntryType, ID) - 1, dt) as dt,
    price
from PricesCTE
order by EntryType
option (maxrecursion 0) -- Infinite recursion, default limit is 100

Here is the SqlFiddle showing this work.

A couple of things:

  • I would think that as the number of records climbs, this might perform better using a Tally table rather than recursion to multiply records. You'd cross-join with the Tally table and have the where clause limit the records according to the RecordCount
  • I don't see how the pricing is supposed to change from the input to the output.
  • I don't know where you are establishing the RecordCount per EntryType, so I've added that into another CTE.