Вывести столбцы, содержащие значения

Недавно встретился вопрос, суть которого сводится к следующему.

Требуется выводить все столбцы таблицы кроме тех, для которых значение у всех записей есть NULL. При этом число столбцов и их имена неизвестны заранее.

Например, для таблицы T

id a    b    c
1  1    NULL NULL
2  NULL NULL b
3  3    NULL NULL

нужно получить

id	a	c
1	1	NULL
2	NULL	b
3	3	NULL


Создадим исходную тестовую таблицу с именем T.

if object_id('T') is not null  drop table T;
create table T(id int identity, a int, b int, c char);
insert into t(a, b, c) values
(1, NULL, NULL),
(NULL, NULL, 'b'),
(3, NULL, NULL);

Я не вижу здесь решения за рамками динамического SQL. Поэтому предлагаю следующий алгоритм:

  1. Находим имена столбцов обращением к информационной схеме, строим курсор.
  2. В цикле обхода курсора, построенного в первом пункте, последовательно выполняем:
    2а) строим запрос, который будет определять, являются ли все значения текущего столбца NULL;
    2б) динамически выполняем запрос из пункта 2а, возвращая результат;
    2в) если результат отрицательный, добавляем к списку выводимых столбцов текущий.
  3. Выполняем динамически запрос на выборку из исходной таблицы, используя полученный в пункте 2 список выводимых столбцов.

Реализация
1.

declare x cursor for
select column_name from information_schema.columns where table_name='t';
open x;

2a) Для определения того, что все значения столбца имеют значение NULL, можно воспользоваться тем, что функция count(имя_столбца) вернет 0 в этом случае.

Теперь перейдем к циклу обхода курсора.

declare @col_name varchar(10), @query_sum nvarchar(100), @res int, @par nvarchar(100);
declare x cursor for
   select column_name from information_schema.columns where table_name='t';
open x;
fetch x into @col_name;
while @@FETCH_STATUS=0
begin
   select @query_sum = N'select @res= count('+@col_name+') from t';
   select @query_sum;
   fetch x into @col_name;
end;
close x;
deallocate x;

Пока мы сформировали в виде строки текста запросы типа

select @res = count(a) from t;

которые последовательно записываются в переменную @query_sum. Для отладки мы выводим эти строки оператором

select @query_sum

2б) Теперь нужно выполнить сформированный запрос и при этом вернуть значение переменной @res. Это можно сделать с помощью процедуры sp_executesql, установив необходимые параметры.

SET @par=N'@res int output';
exec sp_executesql @query_sum, @par, @res output;

2в) Оцениваем значение @res и формируем список возвращаемых столбцов в переменной @query_final, предварительно присвоив ей пустую строку (»):

if (@res is not null) select @query_final += ','+@col_name;

3. При выходе из цикла формируем окончательный запрос и выполняем его

select @query_final = 'select '+ stuff(@query_final,1,1,'') + ' from t;';
execute (@query_final);

Вот весь код, решающий нашу задачу:

declare @col_name varchar(10), @query_sum nvarchar(100), @res int, @par nvarchar(100);
declare @query_final nvarchar(200);
declare x cursor for
  select column_name from information_schema.columns where table_name='t';
open x;
set @query_final ='';
fetch x into @col_name;

while @@FETCH_STATUS=0
begin
  select @query_sum = N'select @res= count('+@col_name+') from t';
  SET @par=N'@res int output';
  exec sp_executesql @query_sum, @par, @res output;
  if (@res != 0) select @query_final += ','+@col_name;
  fetch x into @col_name;
end
close x;
deallocate x;
select @query_final = 'select '+ stuff(@query_final,1,1,'') + ' from t;';
execute (@query_final);

Добавить комментарий