best way to iterate through columns in a SQL table?

Question

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

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, May 22 '18 at 20:19
Correct! I should only change from blank to NULL the columns AFTER the last column with data. does that make sense? — JJ., May 22 '18 at 20:21
It doesn't make sense, but it is clear. I would say that you could figure it out from this page: https://stackoverflow.com/questions/17885667/sql-server-select-column-by-number — Reid, May 22 '18 at 20:23
it varies. so I have to make sure it takes that into account as well. — JJ., May 22 '18 at 20:26
How can the number of columns vary? Can you provide DDL and sample data? — MJH, May 23 '18 at 08:04
Unless you can post something to work with and the desired output I am not putting any more effort into this. — Sean Lange, May 23 '18 at 13:20

score 2 · Accepted Answer · answered May 23 '18 at 08:10

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 |
+----+------+------+------+------+

This is what I was looking for. Thank you. – JJ. May 23 '18 at 17:05 — JJ., May 23 '18 at 17:05

Sean Lange · Answer 2 · 2018-05-22T20:38:31.617

0

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, '')

edited May 22 '18 at 20:38

answered May 22 '18 at 20:15

Sean Lange

33,028
3
25
40

I cannot set columns to NULL that are BEFORE a column with data. Only the blank columns AFTER the last column with data should be converted from blank to NULL. does that make sense? – JJ. May 22 '18 at 20:21
1

Well you have enough rep around here to know that we need sample data to fully understand your question. But you still don't need any loops. I would suggest that with such a strange requirement that the table and what you are modeling are at odds. This is not how relational data works. By definition the order of columns has no meaning. – Sean Lange May 22 '18 at 20:34
@Sean Lange: There is something wrong with your logic in the second update statement, it is concatenating the values together rather than setting them to NULL. – MJH May 23 '18 at 07:59
@MJH right you are...my bad. This is a such a strange requirement with little detail. >. – Sean Lange May 23 '18 at 13:18
1

@Sean Lange: The weird ones are the best, otherwise we'd be writing boring queries on normalised tables. :) – MJH May 23 '18 at 13:39

score 0 · Answer 3 · answered May 22 '18 at 20:35

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?

score 0 · Answer 4 · answered May 23 '18 at 06:31

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 = ''

best way to iterate through columns in a SQL table?

4 Answers4