Solved

Update crosstab data in sql with previous column data

Posted on 2013-05-17
1
113 Views
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
0
Comment
Question by:Fairfield
1 Comment
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39175791
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

930 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

13 Experts available now in Live!

Get 1:1 Help Now