Showing posts with label associated. Show all posts
Showing posts with label associated. Show all posts

Wednesday, March 7, 2012

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 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