MS SQL 2012 : In SQL Shift columns to left side if column contains 0

Question

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.

Input Data:-

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

Expected Output Result:-

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

One static work-around could be:

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

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).

move cells left in sql if left contains null and right contains value

I am planning to build a dynamic solution that can handle the new columns when month-on-month data gets added.

Database is: MS SQL Server 2012.

Quick SQLs To Prepare Data:-

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);

Answer 1

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.

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 |
+---------+-------+-----+-------------+

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.

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.