best way to iterate through columns in a SQL table?

1
votes

I have a table.

Starting from the LAST column, I need to convert blanks to NULLs, UNTIL I reach data.

For example:

Table1
column1, column2, column3, column4, column5

column1, column2, and column3 have data and column4 and column5 have blanks.

So my process would turn column4 and column5 from blanks to NULLs.

Example 2:

Table1
column1, column2, column3, column4, column5

column1, column2, column3 and column4 have data and column5 has blanks.

So my process would turn column5 from blank to NULL.

The way I see it is to have 2 loops. One that goes through each row in the table and then, starting from the last column, updates each column until I reach data.

Does anyone have any tips on how to iterate through each column of a table? I know how to iterate through rows in a table, but I've never iterated through columns in a table.

Thanks

4
sql-serverwhile-loop
In your example what if data is like 1, blank, 2, blank, blank? You want column 2 to still be blank while 4 and 5 are set to null? – SQLChao
Correct! I should only change from blank to NULL the columns AFTER the last column with data. does that make sense? – JJ.
It doesn't make sense, but it is clear. I would say that you could figure it out from this page: stackoverflow.com/questions/17885667/… – Reid
How many total columns? – SQLChao
it varies. so I have to make sure it takes that into account as well. – JJ.

4 Answers

2
votes

This may work for you if there are no NULLs in your data already:

create table t4 (
    id int identity(1,1) not null primary key clustered,
    v1 varchar(10) null,
    v2 varchar(10) null,
    v3 varchar(10) null,
    v4 varchar(10) null
)

insert t4 (v1, v2, v3, v4)
values  ('a', 'b', 'c', 'd'),
        ('a', 'b', 'c', ''),
        ('a', 'b', '', ''),
        ('a', '', '', ''),
        ('', '', '', '')

update  t4
set     v4 = case when v4 = '' then null else v4 end,
        v3 = case when v3 + v4 = '' then null else v3 end,
        v2 = case when v2 + v3 + v4 = '' then null else v2 end,
        v1 = case when v1 + v2 + v3 + v4 = '' then null else v1 end

Before:

+----+----+----+----+----+
| id | v1 | v2 | v3 | v4 |
+----+----+----+----+----+
|  1 | a  | b  | c  | d  |
|  2 | a  | b  | c  |    |
|  3 | a  | b  |    |    |
|  4 | a  |    |    |    |
|  5 |    |    |    |    |
+----+----+----+----+----+

After:

+----+------+------+------+------+
| id |  v1  |  v2  |  v3  |  v4  |
+----+------+------+------+------+
|  1 | a    | b    | c    | d    |
|  2 | a    | b    | c    | NULL |
|  3 | a    | b    | NULL | NULL |
|  4 | a    | NULL | NULL | NULL |
|  5 | NULL | NULL | NULL | NULL |
+----+------+------+------+------+
0
votes

From how I understand it, you're working backwards on the table. The only reason for a column being updated is that it and all ones after it are blank...

Let's say I'm drafting an update statement on your example table, I think it'd go something like this: 

UPDATE table1
SET column5 = CASE WHEN column5 = '' THEN NULL ELSE column5 END 
   ,column4 = CASE WHEN column5 = '' AND column4 = '' THEN NULL ELSE column4 END
   ,column3 = CASE WHEN column5 = '' AND column4 = '' AND column3 = '' THEN NULL ELSE column3 END

...And so forth - the only way column 4 gets NULLed out is if 4 & 5 are both blank.

If you're using sys.tables & sys.columns you can get the column_id and work backwards... probably. The end result that I'm thinking of would be a dynamic SQL statement that's then executed.

It's an interesting problem, and not one I've seen before - what's the use case?

0
votes

Using loops for this is totally the wrong approach. When you think loops you should stop and rethink because loops, especially nested loops, are almost always the wrong way to go.

How about this instead? It will set the entire table in one single statement.

Update Table1
set column1 = nullif(column1, '')
    , column2 = nullif(column2, '')
    , column3 = nullif(column3, '')
    , column4 = nullif(column4, '')
    , column5 = nullif(column5, '')

--EDIT--

With my new understanding maybe you need something like this. But this is a pretty big sign that something is very wrong here. The order of columns in a table should have no meaning. This seems like maybe you repeating groups here which violates 1NF. But without real details it is impossible to say for sure.

Update Table1
set column1 = nullif(column1, '')
    , column2 = nullif(column1 + column2, '')
    , column3 = nullif(column1 + column2 + column3, '')
    , column4 = nullif(column1 + column2 + column3 + column4, '')
    , column5 = nullif(column1 + column2 + column3 + column4 + column5, '')
0
votes

If I understand this correctly, it appears that which columns to be updated, are depend on some condition, which column is blank first.

for example, update column2,3,4,5 if the data only exists in column1 and blank on the column 2.

I can only think the update would be like this :

UPDATE TABLE1
SET COLUMN2 = NULL,
        COLUMN3 = NULL,
        COLUMN4 = NULL,
        COLUMN5 = NULL
WHERE COLUMN2 = ''

UPDATE TABLE1
SET  COLUMN3 = NULL,
        COLUMN4 = NULL,
        COLUMN5 = NULL
WHERE COLUMN3 = ''

UPDATE TABLE1
SET  COLUMN4 = NULL,
        COLUMN5 = NULL,
WHERE COLUMN4 = ''

UPDATE TABLE1
SET  COLUMN5 = NULL
WHERE COLUMN5 = ''