Post reply

Burst a record to multiple copy based on a field of that record

  • September 20, 2011 at 7:07 am

    #148714

    We need help to burst a single record into multiple copies of that source record

    The number of copy is based on a field value of the source record.

    Scenario:

    a course has 10 students of a stated Teacher, I need to burst that course record into 10 copies in a query.

    The query output is then feed to Finance staff for their process and analysis.

    We want to do this in SQL Server 2005 in order to automate the bursting. We prefer control the output to avoid problem in the User Community.

    The source record is in a single table, no join, no duplication.

  • September 20, 2011 at 7:24 am

    #1383837

    that "burst" keyword your using is throwing me off...i'm not sure what you mean.

    you can use a "Tally" or "Numbers" table (search here on SSC) to cross join as many rows as you want:

    SELECT TeacherRecs.*

    from TeacherRecs

    CROSS JOIN (SELECT row_number() OVER(ORDER BY t1.name) As N

    from sys.columns t1 cross join sys.columns t2) Tally

    WHERE Tally.N <= TeacherRecs.StudentCount

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 20, 2011 at 10:14 am

    #1383976

    Thank you, your suggestion works.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply