hana - SQL Table Transformation - using an "if" statement in the "Select" command to split text in a column into their own rows

Question

I have a source date table in this format:

Year  Mon1              Mon2      ...      Mon12
2025  1110001110001101  0010111010110110
2024  000111011011101   0101101110110101

This is the source table. This is a calendar table, and each "1 or 0" in the Month column represents a day, and 1 is for days worked and 0 is for days not worked for each year based off of the US holiday Calendar

I am looking to transform this table into the following Format, as seen in image 2

Year  Month   Day  Working day
2025  01      01   0
2025  01      02   1

For the entire table.

Does anybody know of any way to accomplish this?

question from:https://stackoverflow.com/questions/65862548/sql-table-transformation-using-an-if-statement-in-the-select-command-to-sp

Answer 1

Bottom Up Approach :

Step 1 : Form queries to extract individual Month Columns for a particular year

Step 2 : Apply Split_String SPLIT_TO_TABLE logic to convert columns to rows. Refer this link

Step 3 : Apply case statement for each of these column to row tables to identify if it is a working day or a non-working day.

Step 4 : Union All the result for every month.

Step 5 : Iterate this logic for all years.