SQL Server 2012中不使用Pivot函数实现行转列及Pivot转换结果不符问题解决

我来帮你搞定这两个行转列的需求，先从修正Pivot代码开始，再讲不用Pivot的实现方式。

一、修正Pivot转换代码，得到预期输出 #

你原来的Pivot代码只返回一行，是因为MAX(eType)会把同一日期下同一个event的多个值合并成一个最大值，导致BFSPD的pre-dep被覆盖了。要解决这个问题，我们需要给每个(date, event)组合生成一个唯一的行号，让Pivot时能区分开不同的行。

修正后的完整代码：

CREATE TABLE strategy(date date,[event] varchar(100),eType varchar(100))
INSERT INTO dbo.strategy (DATE, [event], eType) VALUES 
('1 Jan 2018' , 'ABZPD', 'dev'), 
('1 Jan 2018', 'BFSPD', 'stage'), 
('1 Jan 2018', 'BFSPD', 'pre-dep');

-- 修正后的Pivot查询
SELECT DATE, [ABZPD], [BFSPD], [BFSZH], [BHXPD], [BHXZH], [BRSZH], [BRUPQ]
FROM (
    SELECT 
        DATE,
        [event],
        eType,
        -- 给每个(date, event)的组合生成行号，确保同一event的不同值不会被合并
        ROW_NUMBER() OVER(PARTITION BY DATE, [event] ORDER BY eType) AS rn
    FROM [strategy]
    WHERE DATEPART(YEAR, DATE) IN (2018, 2019)
) AS SourceTable
PIVOT (
    MAX(eType) 
    FOR event IN ([ABZPD],[BFSPD] ,[BFSZH] ,[BHXPD] ,[BHXZH] ,[BRSZH] ,[BRUPQ] )
) AS PivotTable
ORDER BY DATE, rn;

DROP TABLE strategy

原理说明：

• 新增的rn列通过ROW_NUMBER()函数，给每个(date, event)分组下的行编号，这样同一日期下的BFSPD两个值会被分配不同的rn。
• Pivot时会自动按DATE和rn分组（因为这两列不在Pivot的聚合或行转列字段里），所以每个rn对应的行都会被单独输出，最终得到你想要的两行结果。

二、不使用Pivot函数实现行转列 #

在SQL Server 2012中，我们可以用CASE WHEN结合分组的方式来实现行转列，核心思路和修正Pivot类似，需要用行号来区分同一日期下的不同行。

完整代码如下：

CREATE TABLE strategy(date date,[event] varchar(100),eType varchar(100))
INSERT INTO dbo.strategy (DATE, [event], eType) VALUES 
('1 Jan 2018' , 'ABZPD', 'dev'), 
('1 Jan 2018', 'BFSPD', 'stage'), 
('1 Jan 2018', 'BFSPD', 'pre-dep');

-- 不使用Pivot的行转列实现
WITH RankedData AS (
    SELECT 
        DATE,
        [event],
        eType,
        -- 给每个日期下的行生成唯一序号，确保不同的行能被分开
        ROW_NUMBER() OVER(PARTITION BY DATE ORDER BY [event], eType) AS rn
    FROM [strategy]
    WHERE DATEPART(YEAR, DATE) IN (2018, 2019)
)
SELECT 
    DATE,
    MAX(CASE WHEN [event] = 'ABZPD' THEN eType ELSE NULL END) AS ABZPD,
    MAX(CASE WHEN [event] = 'BFSPD' THEN eType ELSE NULL END) AS BFSPD,
    MAX(CASE WHEN [event] = 'BFSZH' THEN eType ELSE NULL END) AS BFSZH,
    MAX(CASE WHEN [event] = 'BHXPD' THEN eType ELSE NULL END) AS BHXPD,
    MAX(CASE WHEN [event] = 'BHXZH' THEN eType ELSE NULL END) AS BHXZH,
    MAX(CASE WHEN [event] = 'BRSZH' THEN eType ELSE NULL END) AS BRSZH,
    MAX(CASE WHEN [event] = 'BRUPQ' THEN eType ELSE NULL END) AS BRUPQ
FROM RankedData
GROUP BY DATE, rn
ORDER BY DATE, rn;

DROP TABLE strategy

原理说明：

1. 先用CTE生成带行号的数据集，rn确保同一日期下的每一行都有唯一标识。
2. 用CASE WHEN把每个event值映射到对应的列，MAX()函数用来在分组中只保留当前行对应event的eType值，其他列自动填充NULL。
3. 最后按DATE和rn分组，就能得到和预期一致的行转列结果。

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