well i've only done one piece of it...adding the date;

i have a simple macro i use to dump typical copy/paste data and turn it into a CTE, so coming up with SOMETHING feels better than skipping the question altogether. i try not to pad my post count asking for DDL and sample data too often 😛

the second part i glossed over:

there's a requirement for " whenever it find a “1” in the pattern column. If there is a gap in the pattern it doesn’t need to create a new row."

can be done, but need more than one sample row, and need to know the length of string, whether the preceeding whitespace was relevant, and some more details, but this might get the ball rolling.