CODEWITHSUNDEEP
sqloracle-databaseplsql
RocketEngineerStudent
RocketEngineerStudent

Reputation: 15

(Oracle SQL, PLSQL) How to delete columns, which are entirely NULL

I have a question regarding my Oracle SQL / PLSQL Table: I have a Table with Values. Some of the Columns have no Values in all Rows at all. How do I therefore delete these columns, which are entirely NULL?

incase someones suggestion includes to transpose the tablem its too difficult for me to use Pivot, because the values are too different.

The Table has about 40 columns and 121 Rows.

Thank you very much.

EDIT: what i meant was, it would be great if the empty columns could be "automatically" be detected and deleted/removed/deselected.

Upvotes: 1

Views: 879

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You can identify the columns using aggregation. Here is one method that puts the names in a single column:

select ((case when count(col1) = 0 then 'col1,' end) ||
        (case when count(col2) = 0 then 'col2,' end) ||
        . . .
       ) as cols
from t;

Then you can use alter table to actually drop the columns.

Upvotes: 2

Related Questions