expanding table with additional column with sequential runs

Hi, new to sql and need help with some script.
My current table is a list of names, and a list of associated
lifespans(in months
I would like to add a column (months) that goes from 0 to lifespan.
What table looks like now:
Name Lifespan
a 5
b 3
c 9
...
What I want it to look like:
Name Lifespan Month
a 5 0
a 5 1
...
a 5 5
b 3 0
b 3 1
b 3 2
b 3 3
c 9 0
...
c 9 9
Any help would be greatly appreciated.I don't have sql server installed here to create the script for you but I
can explain what to do - you could populate a lookup table of integers which
has the same number as the max of the lifespan (use a simple while loop to
populate the table, starting at zero). Then do a crossjoin between your base
table and the new lookup table, based on a where condition of lifespan <=
lookup table column and pull back the name, lifespan and lookupcolumn.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Thanks, worked quite well.
-scott
Paul Ibison wrote:
> I don't have sql server installed here to create the script for you but I
> can explain what to do - you could populate a lookup table of integers whi
ch
> has the same number as the max of the lifespan (use a simple while loop to
> populate the table, starting at zero). Then do a crossjoin between your ba
se
> table and the new lookup table, based on a where condition of lifespan <=
> lookup table column and pull back the name, lifespan and lookupcolumn.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com