只选择至少有一个非零行的列。SQL服务器
只选择至少有一个非零行的列。SQL服务器
提问于 2020-04-20 08:09:05
目前，我有一张像下面这样的桌子
+------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+----------+
| country | 0-1 days | 1-2 days | 2-3 days | 3-4 days | 4-5 days | 5-6 days | 6-7 days | 7-8 days | 8-9 days | 9-10 days | >10 days |
+------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+----------+
| country_1 | 1 | 56 | 4 | 6 | 3 | 2 | 0 | 0 | 0 | 0 | 0 |
| country_2 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| country_3 | 1 | 4 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| country_4 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| country_5 | 1 | 3 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| country_6 | 1 | 11 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| country_7 | 1 | 31 | 9 | 4 | 2 | 1 | 0 | 0 | 0 | 0 | 0 |
| country_8 | 1 | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| country_9 | 1 | 13 | 2 | 3 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
| country_10 | 1 | 2 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| country_11 | 1 | 5 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| country_12 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| country_13 | 1 | 5 | 1 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
+------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+----------+我想排除所有具有所有零值的列。所以，我希望产出不会
days
- 10 days
- 7-8天
- 8-9天
- 9-10
使用Server 2016。
如果我有额外的列，编辑，比如country和as primary key。如何使用dynamic sql只选择不是零的列？
回答 2
Stack Overflow用户
发布于 2020-04-20 09:15:32
请参阅下面的示例脚本
首先，您需要找到列名为非零的列表。之后，使用动态查询从表中进行选择。
create table sample_table
(
ID int identity,
col1 int,
col2 int,
col3 int
)
insert into sample_table (col1, col2, col3)
values (1, 12, 0),
(1, 0, 0),
(1, 13, 0),
(1, 14, 0),
(1, 0, 0)
declare @cols nvarchar(max),
@sql nvarchar(max)
select @cols =
case when sum(case when col1 <> 0 then 1 else 0 end) <> 0
then ',col1'
else ''
end
+ case when sum(case when col2 <> 0 then 1 else 0 end) <> 0
then ',col2'
else ''
end
+ case when sum(case when col3 <> 0 then 1 else 0 end) <> 0
then ',col3'
else ''
end
from sample_table
select @sql = 'SELECT ID' + @cols + ' '
+ 'FROM sample_table'
exec sp_executesql @sqlStack Overflow用户
发布于 2020-04-20 16:59:22
您还可以使用Cursor或while循环获取列的详细信息，并动态构建非零和列。
尝试以下几点：
create table test_tab (country varchar(100) ,[0-1 days] int,[1-2 days] int,[2-3 days] int,[3-4 days] int,[4-5 days] int,[5-6 days] int,[6-7 days] int,[7-8 days] int,[8-9 days] int,[9-10 days] int,[>10 days] int)
insert into test_tab select 'country_1' , 1, 56, 4, 6, 3, 2, 0, 0, 0, 0, 0
insert into test_tab select 'country_2' , 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0
insert into test_tab select 'country_3' , 1, 4, 1, 0, 0, 0, 0, 0, 0, 0, 0
insert into test_tab select 'country_4' , 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0
insert into test_tab select 'country_5' , 1, 3, 0, 1, 1, 0, 0, 0, 0, 0, 0
insert into test_tab select 'country_6' , 1, 11, 2, 0, 0, 0, 0, 0, 0, 0, 0
insert into test_tab select 'country_7' , 1, 31, 9, 4, 2, 1, 0, 0, 0, 0, 0
insert into test_tab select 'country_8' , 1, 15, 0, 0, 0, 0, 0, 0, 0, 0, 0
insert into test_tab select 'country_9' , 1, 13, 2, 3, 0, 0, 1, 0, 0, 0, 0
insert into test_tab select 'country_10', 1, 2, 0, 0, 0, 1, 0, 0, 0, 0, 0
insert into test_tab select 'country_11', 1, 5, 1, 0, 1, 0, 0, 0, 0, 0, 0
insert into test_tab select 'country_12', 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0
insert into test_tab select 'country_13', 1, 5, 1, 1, 0, 1, 0, 0, 0, 0, 0
select * from dbname.schemaname.test_tab
declare @non_zero_columns varchar(max) = ''
declare cur cursor fast_forward for
select name from dbname.sys.columns
where object_id = object_id('dbo.test_tab') and name like '%days' --hope there is some kind of pattern
declare @col_name sysname
declare @query nvarchar(max)
declare @result int
open cur
fetch next from cur into @col_name
while @@FETCH_STATUS = 0
begin
set @query = 'set @result = (select sum('+quotename(@col_name)+') from dbname.schemaname.test_tab)'
exec sp_executesql @query, N'@result int out', @result out
if (@result <> 0)
set @non_zero_columns = @non_zero_columns + ',' + quotename(@col_name)
fetch next from cur into @col_name
end
close cur
deallocate cur
declare @final_dynamic_sql varchar(max) = 'select country '+@non_zero_columns+' from dbname.schemaname.test_tab'
print (@final_dynamic_sql)
exec (@final_dynamic_sql)哈哈！
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/61317784
复制相关文章
相似问题
腾讯云开发者
