Вывести столбцы, содержащие значения
Недавно встретился вопрос, суть которого сводится к следующему.
Требуется выводить все столбцы таблицы кроме тех, для которых значение у всех записей есть NULL. При этом число столбцов и их имена неизвестны заранее.
Требуется выводить все столбцы таблицы кроме тех, для которых значение у всех записей есть NULL. При этом число столбцов и их имена неизвестны заранее.
Например, для таблицы T
нужно получить
Создадим исходную тестовую таблицу с именем T.
Я не вижу здесь решения за рамками динамического SQL. Поэтому предлагаю следующий алгоритм:
Реализация
1.
2a) Для определения того, что все значения столбца имеют значение NULL, можно воспользоваться тем, что функция count(имя_столбца) вернет 0 в этом случае.
Теперь перейдем к циклу обхода курсора.
Пока мы сформировали в виде строки текста запросы типа
которые последовательно записываются в переменную @query_sum. Для отладки мы выводим эти строки оператором
2б) Теперь нужно выполнить сформированный запрос и при этом вернуть значение переменной @res. Это можно сделать с помощью процедуры sp_executesql, установив необходимые параметры.
2в) Оцениваем значение @res и формируем список возвращаемых столбцов в переменной @query_final, предварительно присвоив ей пустую строку (''):
3. При выходе из цикла формируем окончательный запрос и выполняем его
Вот весь код, решающий нашу задачу:
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. Поэтому предлагаю следующий алгоритм:
- Находим имена столбцов обращением к информационной схеме, строим курсор.
- В цикле обхода курсора, построенного в первом пункте, последовательно выполняем:
2а) строим запрос, который будет определять, являются ли все значения текущего столбца NULL;
2б) динамически выполняем запрос из пункта 2а, возвращая результат;
2в) если результат отрицательный, добавляем к списку выводимых столбцов текущий. - Выполняем динамически запрос на выборку из исходной таблицы, используя полученный в пункте 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 != 0) 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);
Обратные ссылки
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой