Update crosstab data in sql with previous column data

Posted on 2013-05-17
Last Modified: 2013-10-23
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
Question by:Fairfield
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
LVL 16

Accepted Solution

Surendra Nath earned 500 total points
ID: 39175791
although I say the below code, is not very programmatic, but it should work as required.

,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


Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question