CODEWITHSUNDEEP
sqlsql-servert-sql
Pr0no
Pr0no

Reputation: 4109

Select from a table only the columns that are not empty?

I have a table with hundreds of columns:

------------------------------------------------
ID    ColA    ColB    ColC  Col D   ...   ColZZZ
------------------------------------------------
1             bla
2     foo
3     bar
4                                         baz
------------------------------------------------

I need to know which columns have no values in them (that is: which are empty '' not NULL)

I could create a query for every column:

select count(1) from [table] where [ColA] <> ''; -- returns 2, so not, not empty
select count(1) from [table] where [ColB] <> ''; -- returns 1, so no
select count(1) from [table] where [ColC] <> ''; -- returns 0, so yay! found and empty one
...
etc

But there has to be an easier way for this?

Is there a way to return [table] without the empty columns, in other words:

----------------------------
ID    ColA    ColB    ColZZZ
----------------------------
1             bla
2     foo
3     bar
4                     baz
----------------------------

Upvotes: 0

Views: 2286

Answers (2)

Nicole Douglas
Nicole Douglas

Reputation: 649

How about this to return the table with no empty columns:

SELECT * from table 
WHERE column IS NOT NULL AND TRIM(column) <> ''

This to return the table with empty columns:

SELECT * from table 
WHERE column IS NULL AND TRIM(column) = ''

Upvotes: 0

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Here is solution to it. I used this query before too search for empty columns across all tables. Slightly modified now to search for non-empty, it might have few extra parts not needed in you example.

You create a temp table to store column names that are not empty, and use cursor to create dynamic sql to search for them.

In the end, just generate another dynamic sql to select columns based on temp table results.

IF (OBJECT_ID('tempdb..#tmpRez') IS NOT NULL) DROP TABLE #tmpRez;
CREATE TABLE #tmpRez (TableName sysname, ColName sysname);

DECLARE crs CURSOR LOCAL FAST_FORWARD FOR
    SELECT t.name, c.name FROM sys.tables t
    INNER JOIN  sys.columns c ON c.object_id=t.object_id
    WHERE 1=1
    AND t.name = 'Table1' -- OR your own condition

OPEN crs;

DECLARE @tbl sysname;
DECLARE @col sysname;
DECLARE @sql NVARCHAR(MAX);

FETCH NEXT FROM crs INTO @tbl,@col;

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @sql = 'IF EXISTS (SELECT * FROM ['+ @tbl+'] WHERE [' + @col + '] <> '''') INSERT INTO #tmpRez SELECT ''' + @tbl +''','''+ @col + '''';
    EXEC(@sql);
    FETCH NEXT FROM crs INTO @tbl,@col;
END;

CLOSE crs;
DEALLOCATE crs;   

SELECT @sql = 'SELECT ' + STUFF((SELECT ',' + ColName FROM #tmpRez x
            where x.TableName = y.TableName
    FOR XML PATH ('')), 1, 1, '')  + ' FROM ' + TableName 
FROM #tmpRez y GROUP BY TableName   

EXEC (@sql)

Upvotes: 1

Related Questions