MS SQL 2012:在 SQL 中，如果列包含 0，则将列移至左侧 [英] MS SQL 2012 : In SQL Shift columns to left side if column contains 0
问题描述
如果第一列(左侧列)具有 0 值并且应该在右侧列中添加 NULL，我需要将数据(列)移到左侧.一旦在任何列中发现非零值，则后面列中的 0 值应保持原样.
I need to shift data(columns) to left side if first columns(left side columns) have 0 value and NULL should be added in right side columns. Once non-zero value found in any columns then 0 value in later column should remain as it is.
输入数据:-
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 出现在任何非零/非空值之后).
I could find below lead on Stack Overflow that explains to shift columns to left if all columns are null. But it replaces all NULLs (even if NULL is coming after any non-zero/not null value).
向左移动单元格在 sql 中如果 left 包含 null 并且 right 包含 value
我正计划构建一个动态解决方案，以便在添加月度数据时处理新列.
I am planning to build a dynamic solution that can handle the new columns when month-on-month data gets added.
数据库是:MS SQL Server 2012.
Database is: 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)
This should do what you need (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
它一次对列值进行反透视.
It unpivots the column values a row at a time.
例如 C3 将最终取消透视到
For example C3 will end up unpivoted to
+---------+-------+-----+-------------+
| 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 BY idx) 表达式确保从第一个非零开始的所有值都有 to_preserve设置为 1.
The MAX(IIF(month=0,0,1)) OVER (ORDER BY idx) expression ensures all values from the first non zero one onwards have to_preserve set to 1.
然后它选择带有 to_preserve 标志的值，并使用 ROW_NUMBER 提供一个可用于透视到正确的新列的值.
Then it selects the values with the to_preserve flag and uses ROW_NUMBER to provide a value that can be used for pivoting into the correct new column.
这篇关于MS SQL 2012:在 SQL 中，如果列包含 0，则将列移至左侧的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
