时间戳

首页 > 解决方案 > 我如何/在哪里使用 PIVOT？

sql - 我如何/在哪里使用 PIVOT？

问题描述

我有一个数据表，其中显示单个数据的多行。在下面的示例中，我有一个名为“Location Type”的列，其中显示“BH”和“TPI”。BH 和 TPI 各有一个特定的位置，显示在“位置”列中。我想要两个新列来显示该行的位置数据，而不是为 BH 和 TPI 设置一个新行。我在下面包含了几行数据。我怀疑我需要在这里使用 PIVOT，但我一直很难弄清楚 PIVOT 需要进入的位置。有人可以提供一些指导或向我展示解决方案吗？

这是来自查询的数据示例。

API14 首次生产日期 位置类型 地点
43013540070000 2021 年 2 月 8 日 BH 乡镇 3S 范围 4W 第 17 段 DUCHESNE 县
43013540070000 2021 年 2 月 8 日 TPI 乡镇 3S 范围 4W 第 18 段 DUCHESNE 县

这是我想看到的格式：

API14 首次生产日期 BH 位置 TPI 位置
43013540070000 2021 年 2 月 8 日 乡镇 3S 范围 4W 第 17 段 DUCHESNE 县 乡镇 3S 范围 4W 第 18 段 DUCHESNE 县

到目前为止，这是我的代码：

DECLARE @SearchYear AS VARCHAR(4) = '2021'
DECLARE @SearchMonth AS VARCHAR(2) = '6'

SELECT
    dbo.BuildAPI14(Well.WellID, Construct.SideTrack, Construct.Completion) AS 'API14',
    CAST(ConstructDate.EventDate AS DATE) AS 'First Prod Date',
    Loc.LocType AS 'Location Type',
    CONCAT('Township ',LocExt.Township,LocExt.TownshipDir,' ','Range ',LocExt.Range,LocExt.RangeDir,' Section ',LocExt.Sec,' ',RefCounty.CountyName,' County') AS 'Location',
    tblAPDTracker.SpacingRule AS 'Spacing Rule',
    Lease.Number AS 'Entity Number',
    WellHistory.WHComments AS 'Well History Comments'
FROM Well
    LEFT JOIN Construct ON Construct.WellKey = Well.PKey
    LEFT JOIN ConstructReservoir ON ConstructReservoir.ConstructKey = Construct.PKey
    LEFT JOIN Lease ON Lease.Pkey = ConstructReservoir.LeaseKey
    LEFT JOIN WellHistory ON WellHistory.WellKey = Construct.WellKey
    LEFT JOIN tblAPDTracker ON LEFT(tblAPDTracker.APINO,10) = Well.WellID
    LEFT JOIN Loc ON loc.ConstructKey = Construct.PKey AND Loc.LocType IN ('BH','TPI')
    LEFT JOIN LocExt ON LocExt.LocKey = Loc.PKey
    LEFT JOIN ConstructDate ON ConstructDate.ConstructKey = Construct.PKey AND ConstructDate.Event = 'FirstProduction'
    LEFT JOIN RefCounty ON RefCounty.PKey = LocExt.County
WHERE
    WorkType = 'ENTITY' AND
    WellHistory.ModifyUser = 'UTAH\rachelmedina' AND
    YEAR(WellHistory.ModifyDate) = @SearchYear AND
    MONTH(WellHistory.ModifyDate) = @SearchMonth
GROUP BY
    Well.WellID,
    Construct.SideTrack,
    Construct.Completion,
    ConstructDate.EventDate,
    Loc.LocType,
    LocExt.Township,
    LocExt.TownshipDir,
    LocExt.Range,
    LocExt.RangeDir,
    LocExt.Sec,
    RefCounty.CountyName,
    tblAPDTracker.SpacingRule,
    Lease.Number,
    WellHistory.WHComments,
    WellHistory.ModifyDate
ORDER BY
    Well.WellId,
    WellHistory.ModifyDate DESC

标签： sqlpivotpivot-table

解决方案

Select *
from
(
SELECT
    dbo.BuildAPI14(Well.WellID, Construct.SideTrack, Construct.Completion) AS 'API14',
    CAST(ConstructDate.EventDate AS DATE) AS 'First Prod Date',
    Loc.LocType AS 'Location Type',
    CONCAT('Township ',LocExt.Township,LocExt.TownshipDir,' ','Range ',LocExt.Range,LocExt.RangeDir,' Section ',LocExt.Sec,' ',RefCounty.CountyName,' County') AS 'Location' --,
--    tblAPDTracker.SpacingRule AS 'Spacing Rule',
--    Lease.Number AS 'Entity Number',
--    WellHistory.WHComments AS 'Well History Comments'
FROM Well
    LEFT JOIN Construct ON Construct.WellKey = Well.PKey
    LEFT JOIN ConstructReservoir ON ConstructReservoir.ConstructKey = Construct.PKey
    LEFT JOIN Lease ON Lease.Pkey = ConstructReservoir.LeaseKey
    LEFT JOIN WellHistory ON WellHistory.WellKey = Construct.WellKey
    LEFT JOIN tblAPDTracker ON LEFT(tblAPDTracker.APINO,10) = Well.WellID
    LEFT JOIN Loc ON loc.ConstructKey = Construct.PKey AND Loc.LocType IN ('BH','TPI')
    LEFT JOIN LocExt ON LocExt.LocKey = Loc.PKey
    LEFT JOIN ConstructDate ON ConstructDate.ConstructKey = Construct.PKey AND ConstructDate.Event = 'FirstProduction'
    LEFT JOIN RefCounty ON RefCounty.PKey = LocExt.County
WHERE
    WorkType = 'ENTITY' AND
    WellHistory.ModifyUser = 'UTAH\rachelmedina' AND
    YEAR(WellHistory.ModifyDate) = @SearchYear AND
    MONTH(WellHistory.ModifyDate) = @SearchMonth
GROUP BY
    Well.WellID,
    Construct.SideTrack,
    Construct.Completion,
    ConstructDate.EventDate,
    Loc.LocType,
    LocExt.Township,
    LocExt.TownshipDir,
    LocExt.Range,
    LocExt.RangeDir,
    LocExt.Sec,
    RefCounty.CountyName,
    tblAPDTracker.SpacingRule,
    Lease.Number,
    WellHistory.WHComments,
    WellHistory.ModifyDate
    ) p
pivot (min(p.Location) for [Location Type] in ([TPI], [BH])) pvt

透视列中的值需要一个聚合运算符（sum、avg、min、max 等），所以选择一个像 min 或 max 这样的值，它不会尝试对字符串做任何事情，但如果你在其中多次出现，它会做一些事情旋转列（此处的位置）。

我还注释掉了未出现在示例结果中的选定列，这可能会影响 GROUP BY 子句中需要出现的内容。

推荐阅读