什么SELECT语句将从表行获取非零值?

If I have multiple rows in a table of 27 columns which are all integers like this

如果在一个包含27列的表中有多个行它们都是像这样的整数

id_1    id_2    id_3    id_4    id_5    id_6    id_7    id_8    id_9    id_10   id_11   id_12   id_13   id_14   id_15   id_16   id_17   id_18   id_19   id_20   id_21   id_22   id_23   id_24   id_25   id_26   id_27
0       2       0       4       5       0       0       8       0       10      0       0       0       14      0       0       17      0       0       0       21      0       0       0       0       0       0

and I wanted to run a SELECT statement to get a maximum of 8 columns which are > zero (there will never be more than 8) what would be the best, or at least functional, way of doing it? In the event of there not being 8 values > 0 then NULLS are allowed. So the resulting table from the above would be.

我想要运行一个SELECT语句来获得最大8列的>(永远不会超过8)什么是最好的，或者至少是功能性最好的方法?如果没有8个值>，则允许使用null。所以从上面得到的表格是。

col1 col2 col3 col4 col5 col6 col7 col8
2    4    5    8    10   14   17   21

1 个解决方案

#1

If you can bear with the results being in a column, then this is a simple way:

如果你能忍受结果在一列，那么这是一个简单的方法:

select top 8 v.col
from table t cross apply
     values ((t.id_1), (t.id_2), . . ., (t.id_27)) as v(col)
where v.col <> 0;

This gets 8 for all the data. If you want 8 per row, then you need a row identifier. And you can use window functions:

所有数据都是8。如果您想要每行8，那么您需要一个行标识符。你也可以使用窗口功能:

select t.id, v.col
from (select t.id, v.col,
             row_number() over (partition by t.id order by (select null)) as seqnum
      from table t cross apply
           values ((t.id_1), (t.id_2), . . ., (t.id_27)) as v(col)
      where col <> 0
     ) t
where seqnum <= 8;

Finally, you can pivot these back to a single row. I tend to do this using conditional aggregation:

最后，你可以把它们转回一行。我倾向于使用条件聚合:

select t.id,
       max(case when seqnum = 1 then v.col end) as val1,
       max(case when seqnum = 2 then v.col end) as val2,
       max(case when seqnum = 3 then v.col end) as val3,
       max(case when seqnum = 4 then v.col end) as val4,
       max(case when seqnum = 5 then v.col end) as val5,
       max(case when seqnum = 6 then v.col end) as val6,
       max(case when seqnum = 7 then v.col end) as val7,
       max(case when seqnum = 8 then v.col end) as val8
from (select t.id, v.col,
             row_number() over (partition by t.id order by (select null)) as seqnum
      from table t cross apply
           values ((t.id_1), (t.id_2), . . ., (t.id_27)) as v(col)
      where col <> 0
     ) t
where seqnum <= 8
group by id;

#1