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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now