How to shift columns up or down? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to shift columns up or down?

Author

Topic

mavershang
Posting Yak Master

111 Posts

Posted - 2008-10-07 : 22:50:00

Hi guys,
To illustrate what I want to accomplish, as an example, table test as three columns, which looks like
1 1000 12300
2 1002 5690
3 1003 10000
4 1010 8765

I want to shift the vaules of the third column up so that
the table will look like
1 1000 5690
2 1002 10000
3 1003 8765
4 1010 I donot care, empty or 12300 will be fine

shift the values down is fine too.

Anybody could tell me how to handle it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 23:35:48

[code]UPDATE t1
SET t1.col3=t2.col3
FROM yourtable t1
JOIN yourtable t2
ON t2.col1=t1.col1+1
WHERE t1.col1<=3[/code]

mavershang
Posting Yak Master

111 Posts

Posted - 2008-10-08 : 00:07:31

visakh16, thanks a lot.
could you please explain your query by words?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 00:11:38

quote:
Originally posted by mavershang

visakh16, thanks a lot.
could you please explain your query by words?

i join the table each record to next record by means of ID value (->2,2->3,...). then i set the next records value to prev record (5690->1,10000->2,...)

mavershang
Posting Yak Master

111 Posts

Posted - 2008-10-08 : 00:38:28

Thank you so much.
Here I need a little bit more help from you.

suppose my table is as below

seqid nt ntnumber pdbid res resid
2013 U 1 1022 A 1
2013 A 2 1022 C 2
2013 C 3 1022 C 3
2013 C 4 1022 G 4

I want to shift the former 3 columns up to form

seqid nt ntnumber pdbid res resid
2013 A 2 1022 A 1
2013 C 3 1022 C 2
2013 C 4 1022 C 3
2013 1022 G 4

How could I do that using your query.
Sorry, I am really new in SQL.

mavershang
Posting Yak Master

111 Posts

Posted - 2008-10-08 : 00:52:07

I figured it out. Thanks a lot for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 00:59:11

quote:
Originally posted by mavershang

I figured it out. Thanks a lot for your help

you're welcome

really appreciate your efforts in understanding solution provided.very few people cares to do that.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-08 : 04:15:28

Don't know if this is relevant to you (or if you even still check this thread) but....

visakh16's solutions is workable but bear in mind that if you are using the identity column on one of your tables there is no guarantee that the numbers will actually be sequential. If there are deleted rows then there will be gaps.

Example ([Id], [value])
1, 100
2, 212
3, 123123
5, 1231
6, 123

Here there would be no join for number 3 (because there is no 4)

You can still do as visakh says but you will need to generate either a rownumber (in 2005) or make a temp table with a new identity column and insert all your info in there first.

Ignore if this isn't an issue for you.

-------------
Charlie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 05:46:33

If your id values are not contigous as Charlie suggests, then use below

UPDATE t1
SET t1.col3=t2.col3
FROM yourtable t1
OUTER APPLY(SELECT TOP 1 col3 
            FROM yourtable
            WHERE col1>t1.col1
	    ORDER BY col1 )t2
WHERE  t1.col1<=3
AND t2.col3 IS NOT NULL

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-10-08 : 09:09:52

quote:
Originally posted by mavershang

How to shift columns up or down?

I have a better question:

quote:
Not originally posted by mavershang

WHY to shift columns up or down?

Boycotted Beijing Olympics 2008

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-09 : 05:18:19

quote:
Originally posted by blindman

quote:
Originally posted by mavershang

How to shift columns up or down?

I have a better question:
quote:
Not originally posted by mavershang

WHY to shift columns up or down?

:).

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-09 : 05:23:25

quote:

Originally posted by visakh16

If your id values are not contigous as Charlie suggests, then use below

UPDATE t1
SET t1.col3=t2.col3
FROM yourtable t1
OUTER APPLY(SELECT TOP 1 col3 
            FROM yourtable
            WHERE col1>t1.col1
	    ORDER BY col1 )t2
WHERE  t1.col1<=3
AND t2.col3 IS NOT NULL

Interesting visakh. I have still never used APPLY in anger. I'm right in thinking that this will perform a correlated subquery on each row in the parent table?, kinda like a triangular join?

I would have used ROWNUMBER to make a logical column of sequential numbers and then used your original solution referencing that column instead. It would have been a far longer query to write and not as elegant as using OUTER APPLY but it might be quicker.

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources