在SQL中查询一个混乱的表格

Question

我希望您能协助我编写一个查询语句，将一个我遇到过的最奇怪的表格变成有用的东西。它是一个 SQL 表格（我们称之为“THRESHOLDS”），显示了阈值的每周概况，格式如下：

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

如您所见，有14个与阈值相关的列，每半天一个。在行上，我们有从A到Z的产品，每一天和每个小时都有不同的阈值。

基于以上示例，我想要的是：

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…

有没有什么巧妙的技巧可以用来进行这种转换？我很困惑！

感谢您的关注。 :)

- RFM

---

请在您的问题中加上您正在使用的数据库标签。 - Gordon Linoff

@GordonLinoff 我想当我添加了“SQL”标签时，我已经这样做了。 - RFM

SQL就是查询语言，几乎市面上所有可用的关系型数据库管理系统都在使用它。但这并没有帮助我们解决问题......你需要明确指定你正在使用的是SQL Server、DB2、Oracle、MySQL、PostgreSQL或其他任何可能使用的系统！ - marc_s

不符合第一范式的表格总是很有趣。至少SQL Server、Oracle和Postgres都有（不同的，非标准化的）方法来拆分字符串并将其转换为临时表，然后您可以像正常情况下一样进行连接。这就是为什么指定您的关系型数据库管理系统非常重要的原因。 - 9000

有许多“字符串拆分”函数（SQL Server 2016及更高版本内置的可能不太有用，因为它不允许您对拆分后的字符串结果进行排序），这就是您应该开始的地方。 - pmbAustin

Answer 1

您可以使用 cross apply 进行反转，然后使用 string_split() 和一些字符串操作来处理：

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

这里 是一个 db<>fiddle 示例。

- Gordon Linoff

Answer 2

这很糟糕，但是在这里，只需要两天，你就能明白，调整这里：

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],
    --P.col_hour,
    CASE WHEN P.col_hour LIKE '%PM%' THEN P.[Hour] + 12 ELSE P.[Hour] END [Hour],
    --T.col_threshold,
    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

- Alex