CODEWITHSUNDEEP
sqlpivotcross-apply
Zachary Scott
Zachary Scott

Reputation: 21182

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

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?

Upvotes: 8

Views: 918

Answers (2)

Joel Goodwin
Joel Goodwin

Reputation: 5086

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.

Upvotes: 2

ephemient
ephemient

Reputation: 204778

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;

Upvotes: 3

Related Questions