Querying a messy table in SQL

Question

I could use some help writing a query that will turn one of the weirdest tables I've encountered in something useful. So here it goes.. This is an SQL table (let's call it 'THRESHOLDS') that shows a weekly profile of a threshold. It looks like:

Column 1, 'Product' = X
Column 2, 'Monday_AM' = 0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2
Column 3, 'Monday_PM' = 0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2
...
Column 15, 'Sunday_PM' = 0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2

As you can see, there are 14 columns with threshold values, one column for each half a day. On the rows, we have Products from (let's say) A to Z, all with different threshold values for each day & hour.

What I'd like to have (based on example above) is:

Product     Day     Hour    Threshold
X           1       0       2
X           1       1       2
X           1       2       2
X           1       3       2
X           1       4       2
X           1       5       2
X           1       6       2
X           1       7       2
X           1       8       2
X           1       9       2
X           1       10      2
X           1       11      2
X           1       12      2
X           1       13      2
X           1       14      2
X           1       15      2
X           1       16      2
X           1       17      2
X           1       18      2
X           1       19      2
X           1       20      2
X           1       21      2
X           1       22      2
X           1       23      2
X           2       0       2
X           2       1       2
X           2       2       2
X           2       3       2
etc…

Are there any handy tricks I can use for this type of transformation? I'm struggling!

Thank you for your attention. :)

Answer 1

You can unpivot using cross apply and then use string_split() and some string manipulation:

select t.product, v.day,
       (left(s.value, charindex('-', s.value) - 1)  + v.offset) as hour,
       stuff(s.value, 1, charindex('-', s.value), '')
from t cross apply
     (values (t.monday_am, 1, 0), 
             (t.monday_pm, 1, 12),
             (t.tuesday_am, 2, 0),
             . . .
     ) v(str, day, offset)
     string_split(v.str, ';') s

Here is a db<>fiddle.