文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >在SQL中查询杂乱表

问在SQL中查询杂乱表
EN

Stack Overflow用户
提问于 2020-01-04 00:22:16
回答 2查看 134关注 0票数 3

我可能需要一些帮助来编写一个查询，它将把我所遇到的最奇怪的表之一变成一些有用的东西。所以它开始了..。这是一个SQL表(让我们称它为'THRESHOLDS')，它显示了一个阈值的每周概况。看起来是这样的：

代码语言：javascript
复制
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的产品，每一天和每小时都有不同的阈值。

我想要的(基于上面的例子)是：

代码语言：javascript
复制
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…

对于这种类型的转换，我有什么方便的技巧可以使用吗？我在挣扎！

感谢您的关注。:)

sql
sql-server
delimiter
transpose
EN

回答 2

Stack Overflow用户

发布于 2020-01-04 02:13:56

您可以使用cross apply取消透视，然后使用string_split()和一些字符串操作：

代码语言：javascript
复制
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是一个db<>fiddle。

票数 2
EN

Stack Overflow用户

发布于 2020-01-04 02:59:53

这很糟糕，但在这里，只有两天，你就明白了，fiddle here

代码语言：javascript
复制
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
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/59581918

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档