I have below table and records
create table emp_details(empno int primary key,enname varchar(22),sal int,comm int); insert into emp_details values(1,'ram',4500,200); insert into emp_details(empno,enname) values(2,'kumar'); insert into emp_details(empno)values(3);
Now i do not want to display the rows having null values in the columns except primary key column.
Excepted output Empno Ename Sal Comm 1 ram 4500 200 2 kumar
I used below query to arrive the above result.
select * from emp_details where empno not in(select empno from emp_details where enname is null and sal is null and comm is null)
But my orginal table having 72 columns how can i check all the columns for getting above result.
/
Advertisement
Answer
You could check the length of CONCAT
this way (but it treats empty strings and NULLs the same):
SELECT * FROM dbo.emp_details WHERE LEN(CONCAT(Sal,Comm,...70 more columns)) > 0;
You could build that CONCAT
list this with dynamic SQL:
DECLARE @sql nvarchar(max), @cols nvarchar(max); SELECT @cols = STUFF((SELECT N',' + name FROM sys.columns WHERE [object_id] = OBJECT_ID(N'dbo.emp_details') AND name NOT IN (N'empno', N'enname') ORDER BY column_id FOR XML PATH(''), TYPE).value(N'./text()[1]',N'nvarchar(max)'),1,1,N''); SET @sql = N'SELECT empno, enname, ' + @cols + ' FROM dbo.emp_details WHERE LEN(CONCAT(' + @cols + ')) > 0;'; EXEC sys.sp_executesql @sql;
- Example db<>fiddle