How to get rows whose column values are not null
I want to get the rows of a table so that no column value is zero. No hardcoding of column values. I have hundreds of column names, therefore.
The output should only be line 2, since this entire line has values for all columns. I don't want to point out that all column names are non-zero. This should be done programmatically. Even if I add a new column, it should work without changing the query. This is my vision.
source to share
I found something, but that means using CURSOR
DECLARE @ColumnName VARCHAR(200) DECLARE @ColumnCount INT DECLARE @sql VARCHAR(400) CREATE TABLE #tempTable (Id INT) DECLARE GetNonNullRows CURSOR FOR SELECT c.NAME, (SELECT COUNT(*) FROM sys.columns col WHERE col.object_id = c.OBJECT_ID) FROM sys.tables AS t JOIN sys.columns AS c ON t.object_id = c.object_id WHERE t.name = 'SomeTable' AND t.type = 'U' OPEN GetNonNullRows FETCH NEXT FROM GetNonNullRows INTO @ColumnName, @ColumnCount WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'SELECT st.UniqueId FROM SomeTable AS st WHERE ' + CONVERT(varchar, @ColumnName) + ' IS NOT NULL' INSERT INTO #tempTable EXEC (@sql) FETCH NEXT FROM GetNonNullRows INTO @ColumnName, @ColumnCount END CLOSE GetNonNullRows DEALLOCATE GetNonNullRows SELECT * FROM SomeTable AS st1 WHERE st1.UniqueId IN (SELECT Id FROM #tempTable AS tt GROUP BY Id HAVING COUNT(Id) = @ColumnCount) DROP TABLE #tempTable
Let me explain this a little.
First, I create a cursor that iterates over all the columns in one table. For each column, I am creating a sql script to search the table for non-null values for the selected column. For those rows that meet the criteria, I take its unique identifier and put it in the temp table, and this task I use for all columns.
In the end, only the ID whose count is the same as the number of columns is your result set, because only rows that have the same number of phenomena, such as the number of columns in a table, can be rows with all nonzero values in all columns.
source to share