shifting columns - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

shifting columns

Author

Topic

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2002-12-16 : 13:07:51

I have two tables, one with 6 columns and one with 5. I need to be able to move all the data from the larger table to the smaller table in the same order while shifting data in the process. If a column is null the I need to use the next column to the right and everthiong else need to be shifted as well.

For example

TABLE_1

COL_1 COL_2 COL_3 COL_4 COL_5 COL_6
A B null C D E

Would be inserted into the receiving table with the following values

TABLE_2
COL_1 COL_2 COL_3 COL_4 COL_5
A B C D E

The null can be anywhere and there can be multiple columns with null values.

In a sense I want to coalesce but into distinct columns. I've been looking at this too long and I jknow there's got to be a rather simple solution.

Any takers?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-16 : 13:34:30

OK, I'm assuming that you're naming them COL_1, COL_2 etc. for illustration purposes. If not, then I don't think either table structure is helpful. This is the kind of thing that needs to be normalized. Having a table where "The null can be anywhere and there can be multiple columns with null values" is always a bad thing.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2002-12-16 : 13:53:11

Yes, this is just for illustration purposes. The process is actually a denormalization of data for an interface into a mainframe billing system. The 6 columns are name and address lines and I am rewriting an existing process that is chock full of cursors. I think I've just looked at this one for too long because it seems like it shouldn't be that hard, especially compared to some of the stuff I just wrote but I am having a mental block I guess.

Cat

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-16 : 15:23:35

Eeeeeeeeeeeeewwwwwwwwwwwwww, the multiple address line nightmare. I know it well, sadly.

This should do it:

set nocount on
create table #duh (col1 varchar(10) null, col2 varchar(10) null, col3 varchar(10) null, col4 varchar(10) null)

insert into #duh (col1) values('A')
insert into #duh (col1, col3) values ('a', 'c')
insert into #duh (col1, col4) values ('a', 'b')
insert into #duh (col1, col4) values ('a', 'd')
insert into #duh (col3, col4) values ('c', 'd')
insert into #duh (col2, col4) values ('c', 'd')
insert into #duh (col1, col2, col4) values ('1','2','4')
insert into #duh (col1, col2, col3, col4) values ('1','2','3','4')

SELECT CASE WHEN Datalength(A.Combined) >=10 THEN Rtrim(Substring(A.Combined, 1, 10)) END,
CASE WHEN Datalength(A.Combined) >=20 THEN Rtrim(Substring(A.Combined, 11, 10)) END,
CASE WHEN Datalength(A.Combined) >=30 THEN Rtrim(Substring(A.Combined, 21, 10)) END,
CASE WHEN Datalength(A.Combined) >=40 THEN Rtrim(Substring(A.Combined, 31, 10)) END
FROM
(select
isnull(left(col1 + space(10),10),'') +
isnull(left(col2 + space(10),10),'') +
isnull(left(col3 + space(10),10),'') +
isnull(left(col4 + space(10),10),'') as Combined
from #duh) AS A

drop table #duh

The sub-query (green) consolidates the columns into one string, each section fixed at the length of the column (10 in this example). It substitutes an empty string for null columns, which coalesces nicely. The outer query (red) uses SubString to parse out each section of the string for its corresponding column. Don't ask me why I didn't use CAST or CONVERT, I tried and kept getting problems with it.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2002-12-16 : 15:39:44

Weird. A coworker came up with a similar solution and we were contemplating implementing it by inserting unique characters as delimiters in order to reparse the string. I'm usually pretty wary of using "unique" characters when dealing with free form user data. Eventually there's going to be some smartass like me who is going to enter some special character into the data. This will work nicely.

It's amazing how doing accounting programming can mess with my head and turn it into mush.

Thanks.

Cat

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-16 : 15:51:01

I was thinking of using delimiters too, but the problem of consecutive delimiters reared its ugly head, and removing them during the parse was just gonna be more work.

You can simplify this a little bit by adding a computed column:

ALTER TABLE TABLE_1
ADD Combined AS isnull(left(col_1 + space(10),10),'') +
isnull(left(col_2 + space(10),10),'') +
isnull(left(col_3 + space(10),10),'') +
isnull(left(col_4 + space(10),10),'') +
isnull(left(col_5 + space(10),10),'') +
isnull(left(col_6 + space(10),10),'')

That will let you avoid the subquery portion from the solution.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2002-12-16 : 17:44:28

Actually what I did was change my original insert into a derived table and combined the columns into a string as you so graciously pointed out. Then I updated my table from the values in the derived table using the case statements. Once I got it worked out, tested and indented properly it looks pretty nice. It's a huge chunk of an insert statement but it's fast.

Thanks again.

Cat

Crespo24
Village Idiot

144 Posts

Posted - 2002-12-17 : 04:35:17

quote:

Actually what I did was change my original insert into a derived table and combined the columns into a string as you so graciously pointed out. Then I updated my table from the values in the derived table using the case statements. Once I got it worked out, tested and indented properly it looks pretty nice. It's a huge chunk of an insert statement but it's fast.

Thanks again.

Cat

This would do as well I think..

UPDATE #DUH
SET COL1 = COL2,
COL2 = COL3,
COL3 = COL4,
COL4 = NULL
WHERE COL1 IS NULL

UPDATE #DUH
SET COL2 = COL3,
COL3 = COL4,
COL4 = NULL
WHERE COL2 IS NULL

UPDATE #DUH
SET COL3 = COL4,
COL4 = NULL
WHERE COL3 IS NULL

The only problem with this is if COL1, COL2 are NULL then we have a problem.... The idea is to get something into A first... can you thin of a way to do this?

I think that you will find this update statment work even faster than the inert TBH.

-------------------------

Edited by - Crespo24 on 12/17/2002 04:35:45

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources