Querying a messy table in SQL.

huangapple go评论111阅读模式
英文:

Querying a messy table in SQL

问题

我可以使用一些帮助来编写一个查询,将我遇到的最奇怪的表之一转化为有用的东西。所以就这样吧... 这是一个SQL表(让我们称其为'阈值'),显示了一个阈值的每周概要。它看起来像这样:

  1. 1'产品' = X
  2. 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
  3. 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
  4. ...
  5. 15'星期天下午' = 0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2

正如您所看到的,有14列具有阈值值,每半天一个列。在行上,我们有来自(假设)A到Z的产品,每天和每小时都有不同的阈值值。

根据上面的示例,我想要的是:

  1. 产品 小时 阈值
  2. X 1 0 2
  3. X 1 1 2
  4. X 1 2 2
  5. X 1 3 2
  6. X 1 4 2
  7. X 1 5 2
  8. X 1 6 2
  9. X 1 7 2
  10. X 1 8 2
  11. X 1 9 2
  12. X 1 10 2
  13. X 1 11 2
  14. X 1 12 2
  15. X 1 13 2
  16. X 1 14 2
  17. X 1 15 2
  18. X 1 16 2
  19. X 1 17 2
  20. X 1 18 2
  21. X 1 19 2
  22. X 1 20 2
  23. X 1 21 2
  24. X 1 22 2
  25. X 1 23 2
  26. X 2 0 2
  27. X 2 1 2
  28. X 2 2 2
  29. X 2 3 2
  30. 等等...
  31. 对于这种类型的转换,我可以使用一些有用的技巧吗?我正在努力!
  32. 谢谢您的关注。 :)
  33. <details>
  34. <summary>英文:</summary>
  35. I could use some help writing a query that will turn one of the weirdest tables I&#39;ve encountered in something useful. So here it goes.. This is an SQL table (let&#39;s call it &#39;THRESHOLDS&#39;) 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

  1. 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&#39;s say) A to Z, all with different threshold values for each day &amp; hour.
  2. What I&#39;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…

  1. Are there any handy tricks I can use for this type of transformation? I&#39;m struggling!
  2. Thank you for your attention. :)
  3. </details>
  4. # 答案1
  5. **得分**: 2
  6. 以下是您要翻译的内容:
  7. 您可以使用 `cross apply` 进行反规范化,然后使用 `string_split()` 和一些字符串操作:
  8. select t.product, v.day,
  9. (left(s.value, charindex('-', s.value) - 1) + v.offset) as hour,
  10. stuff(s.value, 1, charindex('-', s.value), '')
  11. from t cross apply
  12. (values (t.monday_am, 1, 0),
  13. (t.monday_pm, 1, 12),
  14. (t.tuesday_am, 2, 0),
  15. . . .
  16. ) v(str, day, offset)
  17. string_split(v.str, ';') s
  18. [这里][1] 是一个 db&lt;&gt;fiddle
  19. <details>
  20. <summary>英文:</summary>
  21. You can unpivot using `cross apply` and then use `string_split()` and some string manipulation:
  22. select t.product, v.day,
  23. (left(s.value, charindex(&#39;-&#39;, s.value) - 1) + v.offset) as hour,
  24. stuff(s.value, 1, charindex(&#39;-&#39;, s.value), &#39;&#39;)
  25. from t cross apply
  26. (values (t.monday_am, 1, 0),
  27. (t.monday_pm, 1, 12),
  28. (t.tuesday_am, 2, 0),
  29. . . .
  30. ) v(str, day, offset)
  31. string_split(v.str, &#39;;&#39;) s
  32. [Here][1] is a db&lt;&gt;fiddle.
  33. [1]: https://dbfiddle.uk/?rdbms=sqlserver_2017&amp;fiddle=3f924c4b99bffad80d69ca5032b9962f
  34. </details>
  35. # 答案2
  36. **得分**: 0
  37. 这是一个SQL查询代码的部分,涉及表格和列名的处理,代码中包括了对日期和时间的操作。如果您需要针对特定部分进行翻译或有任何其他问题,请提出具体的要求。
  38. <details>
  39. <summary>英文:</summary>
  40. This is nasty, but here it is, only with two days, you get the idea, fiddle [here][1]:
  41. declare @t table([Product] varchar(80), [Day] int,
  42. [Monday_AM] varchar(250),
  43. [Monday_PM] varchar(250),
  44. [Tuesday_AM] varchar(250),
  45. [Tuesday_PM] varchar(250));
  46. insert into @t values(&#39;X&#39;, 1,
  47. &#39;0-12;1-22;2-32;3-42;4-52;5-62;6-72;7-82;8-92;9-102;10-112;11-122&#39;,
  48. &#39;0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2&#39;,
  49. &#39;0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2&#39;,
  50. &#39;0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2&#39;)
  51. insert into @t values(&#39;X&#39;, 2,
  52. &#39;0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2&#39;,
  53. &#39;0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2&#39;,
  54. &#39;0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2&#39;,
  55. &#39;0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2&#39;)
  56. insert into @t values(&#39;X&#39;, 3,
  57. &#39;0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2&#39;,
  58. &#39;0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2&#39;,
  59. &#39;0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2&#39;,
  60. &#39;0-2;1-2;2-2;3-2;4-2;5-2;6-2;7-2;8-2;9-2;10-2;11-2&#39;)
  61. ;WITH CTEH
  62. AS
  63. (
  64. select
  65. Product,
  66. [Day],
  67. Substring(&#39;;&#39;+[Monday_AM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 1) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_AM], 1) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 1) - 1)) [Monday_AM_Hour_0],
  68. Substring(&#39;;&#39;+[Monday_AM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 2) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_AM], 2) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 2) - 1)) [Monday_AM_Hour_1],
  69. Substring(&#39;;&#39;+[Monday_AM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 3) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_AM], 3) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 3) - 1)) [Monday_AM_Hour_2],
  70. Substring(&#39;;&#39;+[Monday_AM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 4) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_AM], 4) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 4) - 1)) [Monday_AM_Hour_3],
  71. Substring(&#39;;&#39;+[Monday_AM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 5) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_AM], 5) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 5) - 1)) [Monday_AM_Hour_4],
  72. Substring(&#39;;&#39;+[Monday_AM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 6) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_AM], 6) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 6) - 1)) [Monday_AM_Hour_5],
  73. Substring(&#39;;&#39;+[Monday_AM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 7) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_AM], 7) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 7) - 1)) [Monday_AM_Hour_6],
  74. Substring(&#39;;&#39;+[Monday_AM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 8) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_AM], 8) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 8) - 1)) [Monday_AM_Hour_7],
  75. Substring(&#39;;&#39;+[Monday_AM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 9) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_AM], 9) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 9) - 1)) [Monday_AM_Hour_8],
  76. Substring(&#39;;&#39;+[Monday_AM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 10) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_AM], 10) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 10) - 1)) [Monday_AM_Hour_9],
  77. Substring(&#39;;&#39;+[Monday_AM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 11) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_AM], 11) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 11) - 1)) [Monday_AM_Hour_10],
  78. Substring(&#39;;&#39;+[Monday_AM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 12) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_AM], 12) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_AM], 12) - 1)) [Monday_AM_Hour_11],
  79. Substring(&#39;;&#39;+[Monday_PM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 1) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_PM], 1) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 1) - 1)) [Monday_PM_Hour_0],
  80. Substring(&#39;;&#39;+[Monday_PM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 2) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_PM], 2) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 2) - 1)) [Monday_PM_Hour_1],
  81. Substring(&#39;;&#39;+[Monday_PM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 3) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_PM], 3) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 3) - 1)) [Monday_PM_Hour_2],
  82. Substring(&#39;;&#39;+[Monday_PM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 4) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_PM], 4) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 4) - 1)) [Monday_PM_Hour_3],
  83. Substring(&#39;;&#39;+[Monday_PM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 5) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_PM], 5) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 5) - 1)) [Monday_PM_Hour_4],
  84. Substring(&#39;;&#39;+[Monday_PM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 6) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_PM], 6) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 6) - 1)) [Monday_PM_Hour_5],
  85. Substring(&#39;;&#39;+[Monday_PM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 7) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_PM], 7) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 7) - 1)) [Monday_PM_Hour_6],
  86. Substring(&#39;;&#39;+[Monday_PM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 8) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_PM], 8) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 8) - 1)) [Monday_PM_Hour_7],
  87. Substring(&#39;;&#39;+[Monday_PM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 9) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_PM], 9) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 9) - 1)) [Monday_PM_Hour_8],
  88. Substring(&#39;;&#39;+[Monday_PM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 10) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_PM], 10) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 10) - 1)) [Monday_PM_Hour_9],
  89. Substring(&#39;;&#39;+[Monday_PM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 11) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_PM], 11) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 11) - 1)) [Monday_PM_Hour_10],
  90. Substring(&#39;;&#39;+[Monday_PM], dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 12) + 1, (dbo.[fn_Nth_Pos](&#39;-&#39;, &#39;;&#39;+[Monday_PM], 12) - dbo.[fn_Nth_Pos](&#39;;&#39;, &#39;;&#39;+[Monday_PM], 12) - 1)) [Monday_PM_Hour_11]
  91. From @t
  92. ), CTET AS
  93. (
  94. SELECT
  95. Product,
  96. [Day],
  97. Substring([Monday_AM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 1) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_AM], 1) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 1) - 1)) [Monday_AM_Threshold_0],
  98. Substring([Monday_AM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 2) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_AM], 2) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 2) - 1)) [Monday_AM_Threshold_1],
  99. Substring([Monday_AM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 3) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_AM], 3) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 3) - 1)) [Monday_AM_Threshold_2],
  100. Substring([Monday_AM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 4) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_AM], 4) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 4) - 1)) [Monday_AM_Threshold_3],
  101. Substring([Monday_AM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 5) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_AM], 5) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 5) - 1)) [Monday_AM_Threshold_4],
  102. Substring([Monday_AM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 6) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_AM], 6) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 6) - 1)) [Monday_AM_Threshold_5],
  103. Substring([Monday_AM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 7) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_AM], 7) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 7) - 1)) [Monday_AM_Threshold_6],
  104. Substring([Monday_AM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 8) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_AM], 8) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 8) - 1)) [Monday_AM_Threshold_7],
  105. Substring([Monday_AM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 9) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_AM], 9) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 9) - 1)) [Monday_AM_Threshold_8],
  106. Substring([Monday_AM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 10) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_AM], 10) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 10) - 1)) [Monday_AM_Threshold_9],
  107. Substring([Monday_AM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 11) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_AM], 11) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 11) - 1)) [Monday_AM_Threshold_10],
  108. Substring([Monday_AM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 12) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_AM]+&#39;;&#39;, 12) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_AM], 12) - 1)) [Monday_AM_Threshold_11],
  109. Substring([Monday_PM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 1) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_PM], 1) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 1) - 1)) [Monday_PM_Threshold_0],
  110. Substring([Monday_PM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 2) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_PM], 2) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 2) - 1)) [Monday_PM_Threshold_1],
  111. Substring([Monday_PM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 3) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_PM], 3) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 3) - 1)) [Monday_PM_Threshold_2],
  112. Substring([Monday_PM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 4) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_PM], 4) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 4) - 1)) [Monday_PM_Threshold_3],
  113. Substring([Monday_PM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 5) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_PM], 5) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 5) - 1)) [Monday_PM_Threshold_4],
  114. Substring([Monday_PM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 6) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_PM], 6) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 6) - 1)) [Monday_PM_Threshold_5],
  115. Substring([Monday_PM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 7) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_PM], 7) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 7) - 1)) [Monday_PM_Threshold_6],
  116. Substring([Monday_PM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 8) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_PM], 8) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 8) - 1)) [Monday_PM_Threshold_7],
  117. Substring([Monday_PM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 9) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_PM], 9) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 9) - 1)) [Monday_PM_Threshold_8],
  118. Substring([Monday_PM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 10) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_PM], 10) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 10) - 1)) [Monday_PM_Threshold_9],
  119. Substring([Monday_PM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 11) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_PM], 11) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 11) - 1)) [Monday_PM_Threshold_10],
  120. Substring([Monday_PM], dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 12) + 1, (dbo.[fn_Nth_Pos](&#39;;&#39;, [Monday_PM]+&#39;;&#39;, 12) - dbo.[fn_Nth_Pos](&#39;-&#39;, [Monday_PM], 12) - 1)) [Monday_PM_Threshold_11]
  121. FROM @t
  122. )
  123. SELECT
  124. P.Product,
  125. P.[Day],
  126. --P.col_hour,
  127. CASE WHEN P.col_hour LIKE &#39;%PM%&#39; THEN P.[Hour] + 12 ELSE P.[Hour] END [Hour],
  128. --T.col_threshold,
  129. T.Threshold
  130. FROM
  131. CTEH
  132. UNPIVOT([Hour] for [col_hour] IN (
  133. [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],
  134. [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]
  135. )) P
  136. INNER JOIN
  137. (
  138. SELECT *
  139. FROM
  140. CTET
  141. unpivot([Threshold] FOR [col_threshold] IN (
  142. [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],
  143. [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]
  144. )) T
  145. ) T ON P.Product = T.Product and P.[Day] = T.[Day] and REPLACE(P.col_hour, &#39;Hour&#39;, &#39;Threshold&#39;) = T.col_threshold
  146. [1]: https://dbfiddle.uk/?rdbms=sqlserver_2017&amp;fiddle=55add108c905d8689854ebfb59c33052
  147. </details>

huangapple
  • 本文由 发表于 2020年1月4日 00:22:15
  • 转载请务必保留本文链接:https://go.coder-hub.com/59581918.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定