MS SQL 2012：在 SQL 中，如果列包含 0，则将列移至左侧

如果第一列（左侧列）具有 0 值并且应在右侧列中添加 NULL，我需要将数据（列）移至左侧。 一旦在任何列中发现非零值，则后面列中的 0 值应保持原样。

输入数据：-

cust_id month1  month2  month3  month4  month5
c1      100     200     300     400     500
c2      0       0       50      250     350
c3      0       0       100     0       0
c4      100     0       100     0       500
c5      0       0       0       0       0

预期输出结果：-

cust_id month1  month2  month3  month4  month5
c1      100     200     300     400     500
c2      50      250     350     NULL    NULL
c3      100     0       0       NULL    NULL
c4      100     0       100     0       500
c5      NULL    NULL    NULL    NULL    NULL

一种静态解决方法可能是：

IF month1=0 and month2=0 and month3=0 and month4=0 and month5=0 
THEN INSERT INTO TABLE output_table AS SELECT cust_id,'NULL','NULL','NULL','NULL','NULL' FROM input_table

IF month1=0 and month2=0 and month3=0 and month4=0 and month5 != 0 
THEN INSERT INTO TABLE output_table AS SELECT cust_id,month5,'NULL','NULL','NULL','NULL' FROM input_table

IF month1=0 and month2=0 and month3=0 and month4 != 0 and month5 != 0 
THEN INSERT INTO TABLE output_table AS SELECT cust_id,month4,month5,'NULL','NULL','NULL' FROM input_table

IF month1=0 and month2=0 and month3 !=0  and month4 != 0 and month5 != 0 
THEN INSERT INTO TABLE output_table AS SELECT cust_id,month3,month4,month5,'NULL','NULL' FROM input_table

IF month1 != 0 and month2 != 0 and month3 !=0  and month4 != 0 and month5 != 0 
THEN INSERT INTO TABLE output_table AS SELECT cust_id,month1,month2,month3,month4,month5,'NULL' FROM input_table

我可以在 Stack Overflow 上找到以下线索，解释了如果所有列均为空，则将列向左移动。但它会替换所有 NULL（即使 NULL 出现在任何非零/非空值之后）。

如果左侧包含 null 并且右侧包含值，则在 sql 中向左移动单元格

我计划构建一个动态解决方案，可以在添加逐月数据时处理新列。

数据库是：MS SQL Server 2012。

快速 SQL 准备数据：-

CREATE TABLE input_table(
        cust_id char(5),
        month1 int,
        month2 int,
        month3 int,
        month4 int,
        month5 int
);


INSERT INTO input_table VALUES 
('c1',100,200,300,400,500),
('c2',0,0,50,250,350),
('c3',0,0,100,0,0),
('c4',100,0,100,0,500),
('c5',0,0,0,0,0);

这应该可以满足您的需要（demo)

SELECT i.cust_id,
       oa.*
FROM   input_table i
       OUTER APPLY (SELECT pvt.*
                    FROM   (SELECT month,
                                   col = CONCAT('month', ROW_NUMBER() OVER (ORDER BY idx))
                            FROM   (SELECT month,
                                           idx,
                                           to_preserve = MAX(IIF(month=0,0,1)) OVER (ORDER BY idx)
                                    FROM   (VALUES (1, month1),
                                                   (2, month2),
                                                   (3, month3),
                                                   (4, month4),
                                                   (5, month5) ) V(idx, month)) unpvt
                            WHERE  to_preserve = 1) t 
                            PIVOT (MAX(month) FOR col IN (month1, month2, month3, month4, month5)) pvt
                            ) oa 

它一次将列值逆透视一行。

例如C3最终将转向

+---------+-------+-----+-------------+
| cust_id | month | idx | to_preserve |
+---------+-------+-----+-------------+
| c3      |     0 |   1 |           0 |
| c3      |     0 |   2 |           0 |
| c3      |   100 |   3 |           1 |
| c3      |     0 |   4 |           1 |
| c3      |     0 |   5 |           1 |
+---------+-------+-----+-------------+

The MAX(IIF(month=0,0,1)) OVER (ORDER BY idx)表达式确保从第一个非零开始的所有值都具有to_preserve set to 1.

然后它选择值to_preserve标志和用途ROW_NUMBER提供一个可用于旋转到正确的新列的值。