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

小编典典

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

sql

如果第一列（左侧列）的值为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);

阅读 233

2021-04-22

共1个答案

小编典典

这应该可以满足您的需求（演示）

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提供一个值，该值可用于透视到正确的新列中。

2021-04-22