MSSQL2012：如果列包含0，则在SQLShift中将列移到左侧

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

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