Crazy SQL question: How to do a sort of cross apply with a pivot?

Question

Customer has shortcuts in their data where they have a quantity of records in a field. When I parse them, I need to manufacture records, one for each quantity, incrementing the "identifier" by 7 days (because the number represents a date.

Example: a single product that is on sale for four weeks and I need four records, one product for each week.

 [Event Number]   [Classification]   [Weeks Running]    [Some Data]  
 2009 11 29 00           1                   1         runs one week 
 2009 12 06 00           2                   1         runs one week 
 2009 12 13 00           1                   4        runs four weeks
 2009 12 20 00           2                   4        runs four weeks

Somehow I need to turn this data into the following with a view (sql select) (all in the same table, white space included to see parts:

 [Event Number + Classification]      [Some Data]    
          2009 11 29 01            runs for one week  One week thus one record.
          2009 12 06 02            runs for one week 

          2009 12 13 01           runs for four weeks Repeats 4 times incrementing date by 7
          2009 12 20 01           runs for four weeks
          2009 12 27 01           runs for four weeks
          2009 01 03 01           runs for four weeks

          2009 12 20 02           runs for four weeks Repeats 4 times incrementing date by 7
          2009 12 27 02           runs for four weeks
          2009 01 03 02           runs for four weeks
          2009 01 10 02           runs for four weeks

My thoughts are to have some sort of pivot cross apply sql code?

Isolation

I just want to +1 for the formatting... – Procedure

Answer 1

I have a cheap answer for you. You use a "pre-rendered" Weeks table to create a query-based loop. You would need to put enough weeks into it to cover your expected range of scenarios.

[Week]
1
2
3
4

Then you join your query on [Weeks Running] to this table, using the inequality OriginalTable.WeeksRunning <= Weeks.Week. You end up with one row per week.

You deduce the date by adding Weeks.Week * 7 days to the date embedded in your event number.

Euripus

Answer 2

This is something that would probably be simpler to do on application-side rather than database-side, but I'll give it a shot... This requires a database supporting CTEs, which I don't happen to have on hand, so this is untested.

WITH RECURSIVE expandedTable(eventNumber, classification, index, count, someData)
AS (
    SELECT eventNumber, classification, 1, weeksRunning, someData
    FROM originalTable
    WHERE weeksRunning > 0
  UNION ALL
    SELECT eventNumber + 7, classification, index + 1, count, someData
    FROM expandedTable
    WHERE index < count
)
SELECT eventNumber, classification, someData
FROM expandedTable;

Tmesis

Ah, I love SQL but I hate SQL. It's so true that this is better done app-side. You have to bend over backwards and often end up with less-than-readable queries when trying to pull off something that's just not SQL's thing. I can't see a nice elegant SQL-based solution to this particular problem. – Euripus