mardi 14 juillet 2015

Expand header row into multiple child rows

Within my SQL database I have a table which represents books of tickets [Books] where the number of tickets within a book can vary.

This is represented by two columns [Books].[StartNo] and [Books].[BookSize]

What I need to achieve is a select statement that repeats each row in the table [Books] for each ticket in that book with an additional calculated column that displays the ticket number for that row.

So from

-----------------------------
Book    | StartNo | BookSize 
-----------------------------
Book 1  |   1     |    3     
Book 2  |   4     |    4    
Book 3  |  19     |    4     

to something like this

----------------------------------------
Book    | StartNo | BookSize | TicketNo
----------------------------------------
Book 1  |   1     |    3     |    1
Book 1  |   1     |    3     |    2
Book 1  |   1     |    3     |    3
Book 2  |   4     |    4     |    4
Book 2  |   4     |    4     |    5
Book 2  |   4     |    4     |    6
Book 2  |   4     |    4     |    7
Book 3  |  19     |    4     |   19
Book 3  |  19     |    4     |   20
Book 3  |  19     |    4     |   21
Book 3  |  19     |    4     |   22

I'm just not quite sure where to start.

Aucun commentaire:

Enregistrer un commentaire