如何使用PIVOT实现SQL行转列 将Location Type取值转为独立展示列

解决方案 #

方案1：条件聚合实现（更推荐，语法灵活易调试） #

不需要使用PIVOT，通过CASE WHEN+聚合函数即可实现行转列，修改后的代码如下：

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',
    -- 拆分BH和TPI位置列
    MAX(CASE WHEN Loc.LocType = 'BH' THEN CONCAT('Township ',LocExt.Township,LocExt.TownshipDir,' ','Range ',LocExt.Range,LocExt.RangeDir,' Section ',LocExt.Sec,' ',RefCounty.CountyName,' County') END) AS 'BH Location',
    MAX(CASE WHEN Loc.LocType = 'TPI' THEN CONCAT('Township ',LocExt.Township,LocExt.TownshipDir,' ','Range ',LocExt.Range,LocExt.RangeDir,' Section ',LocExt.Sec,' ',RefCounty.CountyName,' County') END) AS 'TPI 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,
    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

核心修改点：

• 删除原查询中Loc.LocType的输出列与分组项
• 用CASE WHEN判断位置类型，分别拼接位置字符串，再通过MAX聚合消除同一API14下的两行空值，合并为一行输出

方案2：PIVOT实现 #

如果需要用PIVOT语法，需要将你现有查询作为子查询，在外层执行PIVOT操作，示例结构如下：

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

SELECT 
    API14,
    [First Prod Date],
    [BH] AS 'BH Location',
    [TPI] AS 'TPI Location',
    [Spacing Rule],
    [Entity Number],
    [Well History Comments]
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
) AS SourceData
PIVOT (
    MAX(Location) -- 聚合函数，合并位置值
    FOR [Location Type] IN ([BH],[TPI]) -- 指定要转成列的位置类型取值
) AS PivotTable
ORDER BY
    API14,
    [Well History Comments] DESC -- 内层排序在外层不生效，需移到外层

内容的提问来源于stack exchange，提问作者Matt