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

苟延残喘

如果第一列（左侧列）的值为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
我可以在下面找到线索，该线索解释了如果所有列都为空，则将列向左移动。但是它将替换所有NULL（即使NULL出现在任何非零/非null值之后）。
我计划构建一个动态解决方案，以便在添加按月数据时可以处理新列。
[如果left包含null，right包含value，则将单元格在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);

解决方案:


            这应该可以满足您的需求（演示）
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 |
+---------+-------+-----+-------------+
该MAX(IIF(month=0,0,1)) OVER (ORDER BYidx)表达式确保从第一个非零开始的所有值都to_preserve设置为1。
然后，它to_preserve使用标志选择值，并用于ROW_NUMBER提供一个值，该值可用于透视到正确的新列中。

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

苟延残喘 LV1