CODEWITHSUNDEEP
sqlsql-server
Marie Cécile MAOUI
Marie Cécile MAOUI

Reputation: 11

Drop empty column SQL Server

I need to remove all the empty column for a given table.
As it could by any table, I do not know the name of the columns.

For example, the input table is table1 :

Id Value1 Value2 Value3 Value4
1 Cell 2 NULL Cell 2 NULL
2 Cell 4 NULL Cell 4 NULL

The output table should be :

Id Value1 Value3
1 Cell 2 Cell 2
2 Cell 4 Cell 4

Upvotes: 1

Views: 321

Answers (1)

Sergio Meza
Sergio Meza

Reputation: 11

You can try that.

set @sql = null;

select concat_ws(', ',
    case when count(nullif(ID, ''))       > 0 then 'ID'       end,
    case when count(nullif(Value1, '')) > 0 then 'Value1' end,
    case when count(nullif(Value2, ''))    > 0 then 'Value2'    end,
    case when count(nullif(Value3, ''))         > 0 then 'Value3'         end,
    case when count(nullif(Value4, ''))    > 0 then 'Value4'    end
) into @sql
from table1 ;

set @sql = concat('select ', @sql, ' from imported_data where',
                 (
                    SELECT INSERT( GROUP_CONCAT('OR `', `COLUMN_NAME`, '`  != \'\' ' SEPARATOR ' '), 1, 3, '')
                    FROM `information_schema`.`COLUMNS`
                    WHERE `TABLE_SCHEMA` = 'mydb'
                        AND `TABLE_NAME` = 'table1'
                )
);  
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

The nullif clause checks if there are any strictly empty fields, if you want to include null values you will have to remove the nullif.

Upvotes: 0

Related Questions