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
Showing posts with label names. Show all posts
Showing posts with label names. 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 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
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
Subscribe to:
Posts (Atom)