By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

Moving Data from right to left SQL

P: 9
Apologies if the title is a bit abstract, but it was hard to put into a single title what I am trying to achieve.

I have a table called PTYP and columns named PTYPNR, STAB1, STAB2, STAB3.......to STAB10

In some of the records one of the STAB columns (could be any of them) can have an entry of 1659 what I am trying to do is delete this entry but then move the values in the columns to the right 1 to the left

e.g Say the existing record layout is
PTYP STAB1 STAB2 STAB3 STAB4 STAB5............ STAB10
1 400 300 1659 700 0 0
should become
1 400 300 700 0 0.............. 0

what I am getting is
1 400 300 300

I am using the following query

update protel.ptyp set
[stab1]= case when [stab1]=1659 then [stab2] else [stab1] end
,[stab2]= case when [stab2]=1659 or [Stab2]= [stab1] then [stab3] else [stab2] end
,[stab3]= case when [stab3]=1659 or [Stab3]= [stab2] then [stab4] else [stab3] end
,[stab4]= case when [Stab4]=1659 or [Stab4]= [stab3] then [Stab5] else [Stab4] end
,[Stab5]= case when [Stab5]=1659 or [Stab5]= [stab4] then [Stab6] else [Stab5] end
,[Stab6]= case when [Stab6]=1659 or [Stab6]= [stab5] then [Stab7] else [Stab6] end
,[Stab7]= case when [Stab7]=1659 or [Stab7]= [stab6] then [Stab8] else [Stab7] end
,[Stab8]= case when [Stab8]=1659 or [Stab8]= [stab7] then [Stab9] else [Stab8] end
,[Stab9]= case when [Stab9]=1659 or [Stab9]= [stab8] then [Stab10] else [Stab9] end
,[Stab10]= case when [Stab10]=1659 or [Stab10]= [stab9] then 0 else [Stab10] end


Can anyone provide the correct code?
thank you
Nov 27 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.