这很糟糕,但是在这里,只需要两天,你就能明白,调整这里:
declare @t table([Product] varchar(80), [Day] int,
[Monday_AM] varchar(250),
[Monday_PM] varchar(250),
[Tuesday_AM] varchar(250),
[Tuesday_PM] varchar(250));
insert into @t values('X', 1,
'0-12;1-22;2-32;3-42;4-52;5-62;6-72;7-82;8-92;9-102;10-112;11-122',
'0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2',
'0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2',
'0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2')
insert into @t values('X', 2,
'0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2',
'0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2',
'0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2',
'0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2')
insert into @t values('X', 3,
'0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2',
'0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2',
'0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2',
'0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2')
;WITH CTEH
AS
(
select
Product,
[Day],
Substring(';'+[Monday_AM], dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 1) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_AM], 1) - dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 1) - 1)) [Monday_AM_Hour_0],
Substring(';'+[Monday_AM], dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 2) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_AM], 2) - dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 2) - 1)) [Monday_AM_Hour_1],
Substring(';'+[Monday_AM], dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 3) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_AM], 3) - dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 3) - 1)) [Monday_AM_Hour_2],
Substring(';'+[Monday_AM], dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 4) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_AM], 4) - dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 4) - 1)) [Monday_AM_Hour_3],
Substring(';'+[Monday_AM], dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 5) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_AM], 5) - dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 5) - 1)) [Monday_AM_Hour_4],
Substring(';'+[Monday_AM], dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 6) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_AM], 6) - dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 6) - 1)) [Monday_AM_Hour_5],
Substring(';'+[Monday_AM], dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 7) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_AM], 7) - dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 7) - 1)) [Monday_AM_Hour_6],
Substring(';'+[Monday_AM], dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 8) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_AM], 8) - dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 8) - 1)) [Monday_AM_Hour_7],
Substring(';'+[Monday_AM], dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 9) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_AM], 9) - dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 9) - 1)) [Monday_AM_Hour_8],
Substring(';'+[Monday_AM], dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 10) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_AM], 10) - dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 10) - 1)) [Monday_AM_Hour_9],
Substring(';'+[Monday_AM], dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 11) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_AM], 11) - dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 11) - 1)) [Monday_AM_Hour_10],
Substring(';'+[Monday_AM], dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 12) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_AM], 12) - dbo.[fn_Nth_Pos](';', ';'+[Monday_AM], 12) - 1)) [Monday_AM_Hour_11],
Substring(';'+[Monday_PM], dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 1) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_PM], 1) - dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 1) - 1)) [Monday_PM_Hour_0],
Substring(';'+[Monday_PM], dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 2) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_PM], 2) - dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 2) - 1)) [Monday_PM_Hour_1],
Substring(';'+[Monday_PM], dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 3) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_PM], 3) - dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 3) - 1)) [Monday_PM_Hour_2],
Substring(';'+[Monday_PM], dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 4) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_PM], 4) - dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 4) - 1)) [Monday_PM_Hour_3],
Substring(';'+[Monday_PM], dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 5) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_PM], 5) - dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 5) - 1)) [Monday_PM_Hour_4],
Substring(';'+[Monday_PM], dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 6) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_PM], 6) - dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 6) - 1)) [Monday_PM_Hour_5],
Substring(';'+[Monday_PM], dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 7) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_PM], 7) - dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 7) - 1)) [Monday_PM_Hour_6],
Substring(';'+[Monday_PM], dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 8) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_PM], 8) - dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 8) - 1)) [Monday_PM_Hour_7],
Substring(';'+[Monday_PM], dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 9) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_PM], 9) - dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 9) - 1)) [Monday_PM_Hour_8],
Substring(';'+[Monday_PM], dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 10) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_PM], 10) - dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 10) - 1)) [Monday_PM_Hour_9],
Substring(';'+[Monday_PM], dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 11) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_PM], 11) - dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 11) - 1)) [Monday_PM_Hour_10],
Substring(';'+[Monday_PM], dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 12) + 1, (dbo.[fn_Nth_Pos]('-', ';'+[Monday_PM], 12) - dbo.[fn_Nth_Pos](';', ';'+[Monday_PM], 12) - 1)) [Monday_PM_Hour_11]
From @t
), CTET AS
(
SELECT
Product,
[Day],
Substring([Monday_AM], dbo.[fn_Nth_Pos]('-', [Monday_AM], 1) + 1, (dbo.[fn_Nth_Pos](';', [Monday_AM], 1) - dbo.[fn_Nth_Pos]('-', [Monday_AM], 1) - 1)) [Monday_AM_Threshold_0],
Substring([Monday_AM], dbo.[fn_Nth_Pos]('-', [Monday_AM], 2) + 1, (dbo.[fn_Nth_Pos](';', [Monday_AM], 2) - dbo.[fn_Nth_Pos]('-', [Monday_AM], 2) - 1)) [Monday_AM_Threshold_1],
Substring([Monday_AM], dbo.[fn_Nth_Pos]('-', [Monday_AM], 3) + 1, (dbo.[fn_Nth_Pos](';', [Monday_AM], 3) - dbo.[fn_Nth_Pos]('-', [Monday_AM], 3) - 1)) [Monday_AM_Threshold_2],
Substring([Monday_AM], dbo.[fn_Nth_Pos]('-', [Monday_AM], 4) + 1, (dbo.[fn_Nth_Pos](';', [Monday_AM], 4) - dbo.[fn_Nth_Pos]('-', [Monday_AM], 4) - 1)) [Monday_AM_Threshold_3],
Substring([Monday_AM], dbo.[fn_Nth_Pos]('-', [Monday_AM], 5) + 1, (dbo.[fn_Nth_Pos](';', [Monday_AM], 5) - dbo.[fn_Nth_Pos]('-', [Monday_AM], 5) - 1)) [Monday_AM_Threshold_4],
Substring([Monday_AM], dbo.[fn_Nth_Pos]('-', [Monday_AM], 6) + 1, (dbo.[fn_Nth_Pos](';', [Monday_AM], 6) - dbo.[fn_Nth_Pos]('-', [Monday_AM], 6) - 1)) [Monday_AM_Threshold_5],
Substring([Monday_AM], dbo.[fn_Nth_Pos]('-', [Monday_AM], 7) + 1, (dbo.[fn_Nth_Pos](';', [Monday_AM], 7) - dbo.[fn_Nth_Pos]('-', [Monday_AM], 7) - 1)) [Monday_AM_Threshold_6],
Substring([Monday_AM], dbo.[fn_Nth_Pos]('-', [Monday_AM], 8) + 1, (dbo.[fn_Nth_Pos](';', [Monday_AM], 8) - dbo.[fn_Nth_Pos]('-', [Monday_AM], 8) - 1)) [Monday_AM_Threshold_7],
Substring([Monday_AM], dbo.[fn_Nth_Pos]('-', [Monday_AM], 9) + 1, (dbo.[fn_Nth_Pos](';', [Monday_AM], 9) - dbo.[fn_Nth_Pos]('-', [Monday_AM], 9) - 1)) [Monday_AM_Threshold_8],
Substring([Monday_AM], dbo.[fn_Nth_Pos]('-', [Monday_AM], 10) + 1, (dbo.[fn_Nth_Pos](';', [Monday_AM], 10) - dbo.[fn_Nth_Pos]('-', [Monday_AM], 10) - 1)) [Monday_AM_Threshold_9],
Substring([Monday_AM], dbo.[fn_Nth_Pos]('-', [Monday_AM], 11) + 1, (dbo.[fn_Nth_Pos](';', [Monday_AM], 11) - dbo.[fn_Nth_Pos]('-', [Monday_AM], 11) - 1)) [Monday_AM_Threshold_10],
Substring([Monday_AM], dbo.[fn_Nth_Pos]('-', [Monday_AM], 12) + 1, (dbo.[fn_Nth_Pos](';', [Monday_AM]+';', 12) - dbo.[fn_Nth_Pos]('-', [Monday_AM], 12) - 1)) [Monday_AM_Threshold_11],
Substring([Monday_PM], dbo.[fn_Nth_Pos]('-', [Monday_PM], 1) + 1, (dbo.[fn_Nth_Pos](';', [Monday_PM], 1) - dbo.[fn_Nth_Pos]('-', [Monday_PM], 1) - 1)) [Monday_PM_Threshold_0],
Substring([Monday_PM], dbo.[fn_Nth_Pos]('-', [Monday_PM], 2) + 1, (dbo.[fn_Nth_Pos](';', [Monday_PM], 2) - dbo.[fn_Nth_Pos]('-', [Monday_PM], 2) - 1)) [Monday_PM_Threshold_1],
Substring([Monday_PM], dbo.[fn_Nth_Pos]('-', [Monday_PM], 3) + 1, (dbo.[fn_Nth_Pos](';', [Monday_PM], 3) - dbo.[fn_Nth_Pos]('-', [Monday_PM], 3) - 1)) [Monday_PM_Threshold_2],
Substring([Monday_PM], dbo.[fn_Nth_Pos]('-', [Monday_PM], 4) + 1, (dbo.[fn_Nth_Pos](';', [Monday_PM], 4) - dbo.[fn_Nth_Pos]('-', [Monday_PM], 4) - 1)) [Monday_PM_Threshold_3],
Substring([Monday_PM], dbo.[fn_Nth_Pos]('-', [Monday_PM], 5) + 1, (dbo.[fn_Nth_Pos](';', [Monday_PM], 5) - dbo.[fn_Nth_Pos]('-', [Monday_PM], 5) - 1)) [Monday_PM_Threshold_4],
Substring([Monday_PM], dbo.[fn_Nth_Pos]('-', [Monday_PM], 6) + 1, (dbo.[fn_Nth_Pos](';', [Monday_PM], 6) - dbo.[fn_Nth_Pos]('-', [Monday_PM], 6) - 1)) [Monday_PM_Threshold_5],
Substring([Monday_PM], dbo.[fn_Nth_Pos]('-', [Monday_PM], 7) + 1, (dbo.[fn_Nth_Pos](';', [Monday_PM], 7) - dbo.[fn_Nth_Pos]('-', [Monday_PM], 7) - 1)) [Monday_PM_Threshold_6],
Substring([Monday_PM], dbo.[fn_Nth_Pos]('-', [Monday_PM], 8) + 1, (dbo.[fn_Nth_Pos](';', [Monday_PM], 8) - dbo.[fn_Nth_Pos]('-', [Monday_PM], 8) - 1)) [Monday_PM_Threshold_7],
Substring([Monday_PM], dbo.[fn_Nth_Pos]('-', [Monday_PM], 9) + 1, (dbo.[fn_Nth_Pos](';', [Monday_PM], 9) - dbo.[fn_Nth_Pos]('-', [Monday_PM], 9) - 1)) [Monday_PM_Threshold_8],
Substring([Monday_PM], dbo.[fn_Nth_Pos]('-', [Monday_PM], 10) + 1, (dbo.[fn_Nth_Pos](';', [Monday_PM], 10) - dbo.[fn_Nth_Pos]('-', [Monday_PM], 10) - 1)) [Monday_PM_Threshold_9],
Substring([Monday_PM], dbo.[fn_Nth_Pos]('-', [Monday_PM], 11) + 1, (dbo.[fn_Nth_Pos](';', [Monday_PM], 11) - dbo.[fn_Nth_Pos]('-', [Monday_PM], 11) - 1)) [Monday_PM_Threshold_10],
Substring([Monday_PM], dbo.[fn_Nth_Pos]('-', [Monday_PM], 12) + 1, (dbo.[fn_Nth_Pos](';', [Monday_PM]+';', 12) - dbo.[fn_Nth_Pos]('-', [Monday_PM], 12) - 1)) [Monday_PM_Threshold_11]
FROM @t
)
SELECT
P.Product,
P.[Day],
CASE WHEN P.col_hour LIKE '%PM%' THEN P.[Hour] + 12 ELSE P.[Hour] END [Hour],
T.Threshold
FROM
CTEH
UNPIVOT([Hour] for [col_hour] IN (
[Monday_AM_Hour_0],[Monday_AM_Hour_1],[Monday_AM_Hour_2],[Monday_AM_Hour_3],[Monday_AM_Hour_4],[Monday_AM_Hour_5],[Monday_AM_Hour_6],[Monday_AM_Hour_7],[Monday_AM_Hour_8],[Monday_AM_Hour_9],[Monday_AM_Hour_10],[Monday_AM_Hour_11],
[Monday_PM_Hour_0],[Monday_PM_Hour_1],[Monday_PM_Hour_2],[Monday_PM_Hour_3],[Monday_PM_Hour_4],[Monday_PM_Hour_5],[Monday_PM_Hour_6],[Monday_PM_Hour_7],[Monday_PM_Hour_8],[Monday_PM_Hour_9],[Monday_PM_Hour_10],[Monday_PM_Hour_11]
)) P
INNER JOIN
(
SELECT *
FROM
CTET
unpivot([Threshold] FOR [col_threshold] IN (
[Monday_AM_Threshold_0],[Monday_AM_Threshold_1],[Monday_AM_Threshold_2],[Monday_AM_Threshold_3],[Monday_AM_Threshold_4],[Monday_AM_Threshold_5],[Monday_AM_Threshold_6],[Monday_AM_Threshold_7],[Monday_AM_Threshold_8],[Monday_AM_Threshold_9],[Monday_AM_Threshold_10],[Monday_AM_Threshold_11],
[Monday_PM_Threshold_0],[Monday_PM_Threshold_1],[Monday_PM_Threshold_2],[Monday_PM_Threshold_3],[Monday_PM_Threshold_4],[Monday_PM_Threshold_5],[Monday_PM_Threshold_6],[Monday_PM_Threshold_7],[Monday_PM_Threshold_8],[Monday_PM_Threshold_9],[Monday_PM_Threshold_10],[Monday_PM_Threshold_11]
)) T
) T ON P.Product = T.Product and P.[Day] = T.[Day] and REPLACE(P.col_hour, 'Hour', 'Threshold') = T.col_threshold