Update crosstab data in sql with previous column data

Sample tableI have a table pl_combined_data that I would like to have additional columns of data updated from data in the same row.  If there is data in the column, I would like it to use the new data to update following columns of data.

For example (original table):

PN                   2000         2001      2002     2003     2004

100000-001     88             NULL     NULL     90
100001-001     NULL        87          87          NULL    88
100002-001     NULL        NULL     NULL      88         NULL


Desired output

PN                   2000         2001      2002     2003     2004

100000-001     88             88         88          90        90
100001-001     NULL        87          87          87        88
100002-001     NULL        NULL     NULL      88        88
FairfieldAsked:
Who is Participating?
 
Surendra NathConnect With a Mentor Technology LeadCommented:
although I say the below code, is not very programmatic, but it should work as required.

select 
PN
,[1997]
,COALESCE([1998],[1997])		as [1998]
,COALESCE([1999],[1998],[1997]) as [1999]
,COALESCE([2000],[1999],[1998],[1997]) as [2000]
,COALESCE([2001],[2000],[1999],[1998],[1997]) as [2001]
,COALESCE([2002],[2001],[2000],[1999],[1998],[1997]) as [2002]
,COALESCE([2003],[2002],[2001],[2000],[1999],[1998],[1997]) as [2003]
,COALESCE([2004],[2003],[2002],[2001],[2000],[1999],[1998],[1997]) as [2004]
,COALESCE([2005],[2004],[2003],[2002],[2001],[2000],[1999],[1998],[1997]) as [2005]
,COALESCE([2006],[2005],[2004],[2003],[2002],[2001],[2000],[1999],[1998],[1997]) as [2006]
,COALESCE([2007],[2006],[2005],[2004],[2003],[2002],[2001],[2000],[1999],[1998],[1997]) as [2007]
,COALESCE([2008],[2007],[2006],[2005],[2004],[2003],[2002],[2001],[2000],[1999],[1998],[1997]) as [2008]
,COALESCE([2009],[2008],[2007],[2006],[2005],[2004],[2003],[2002],[2001],[2000],[1999],[1998],[1997]) as [2009]
,COALESCE([2010],[2009],[2008],[2007],[2006],[2005],[2004],[2003],[2002],[2001],[2000],[1999],[1998],[1997]) as [2010]
,COALESCE([2011],[2010],[2009],[2008],[2007],[2006],[2005],[2004],[2003],[2002],[2001],[2000],[1999],[1998],[1997]) as [2011]
,COALESCE([2012],[2011],[2010],[2009],[2008],[2007],[2006],[2005],[2004],[2003],[2002],[2001],[2000],[1999],[1998],[1997]) as [2012]
,COALESCE([2013],[2012],[2011],[2010],[2009],[2008],[2007],[2006],[2005],[2004],[2003],[2002],[2001],[2000],[1999],[1998],[1997]) as [2013]
from pl_combined_data 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.