Solved

Update crosstab data in sql with previous column data

Posted on 2013-05-17
1
122 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
[X]
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

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…

690 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