sql query to exclude not null columns-CodePudding

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.

CodePudding user response：

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'.[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