如何将日期行值转为列名？现有动态SQL方法失效求助

看起来你是想实现**行转列（Pivot）**的效果，把VISIT_DATE字段的取值转为列名，同时统计每个地区在对应日期的符合条件的记录数对吧？先看看你现有语句里的问题，再给你两种可行的方案：

一、现有静态CASE语句的问题 #

你写的代码里有两处明显错误，这会导致结果不符合预期：

1. 列别名与条件不匹配：比如SUM(case when VISIT_DATE='1-2-2019' then 1 else 0 end) as '2-2-2019'，条件里是1-2-2019，但别名写成了2-2-2019，同理最后一行的1-31-2019别名写成2-31-2019，这会让列名和对应的日期完全错位。
2. 日期格式匹配问题：如果VISIT_DATE是日期类型而非字符串，直接用'1-1-2019'这种字符串匹配可能会因为数据库的日期格式设置出现转换错误，建议用日期函数统一格式后再匹配。

二、修正后的静态CASE语句 #

如果你的日期范围固定，可以用修正后的静态CASE语句实现，下面分两种常见数据库给出示例：

MySQL 版本 #

SELECT LOCATION
    , SUM(CASE WHEN DATE(VISIT_DATE) = '2019-01-01' THEN 1 ELSE 0 END) AS '1-1-2019'
    , SUM(CASE WHEN DATE(VISIT_DATE) = '2019-01-02' THEN 1 ELSE 0 END) AS '1-2-2019'
    -- 按需要依次添加所有日期的CASE语句
    , SUM(CASE WHEN DATE(VISIT_DATE) = '2019-01-31' THEN 1 ELSE 0 END) AS '1-31-2019'
FROM testing_table 
WHERE APPOINTMENT_TYPE = 'REG'
GROUP BY LOCATION 
ORDER BY LOCATION;

SQL Server 版本 #

SELECT LOCATION
    , SUM(CASE WHEN CONVERT(date, VISIT_DATE) = '2019-01-01' THEN 1 ELSE 0 END) AS [1-1-2019]
    , SUM(CASE WHEN CONVERT(date, VISIT_DATE) = '2019-01-02' THEN 1 ELSE 0 END) AS [1-2-2019]
    -- 按需要依次添加所有日期的CASE语句
    , SUM(CASE WHEN CONVERT(date, VISIT_DATE) = '2019-01-31' THEN 1 ELSE 0 END) AS [1-31-2019]
FROM testing_table 
WHERE APPOINTMENT_TYPE = 'REG'
GROUP BY LOCATION 
ORDER BY LOCATION;

三、动态SQL方案（自动适配所有日期） #

如果日期范围不固定，手动写所有CASE语句太麻烦，用动态SQL可以自动获取所有不同的日期并生成对应列，下面是两种数据库的实现：

MySQL 动态SQL #

SET @sql = NULL;
-- 拼接所有日期对应的CASE语句片段
SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
        'SUM(CASE WHEN DATE(VISIT_DATE) = ''',
        DATE(VISIT_DATE),
        ''' THEN 1 ELSE 0 END) AS ''',
        DATE_FORMAT(VISIT_DATE, '%e-%c-%Y'),
        ''''
    )
) INTO @sql
FROM testing_table
WHERE APPOINTMENT_TYPE = 'REG';

-- 拼接完整的SQL语句
SET @sql = CONCAT('SELECT LOCATION, ', @sql, ' FROM testing_table WHERE APPOINTMENT_TYPE = ''REG'' GROUP BY LOCATION ORDER BY LOCATION');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server 动态SQL #

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'SELECT LOCATION'

-- 拼接每个日期对应的CASE语句
SELECT @sql = @sql + N', SUM(CASE WHEN CONVERT(date, VISIT_DATE) = ''' + CONVERT(NVARCHAR, VISIT_DATE, 103) + ''' THEN 1 ELSE 0 END) AS [' + CONVERT(NVARCHAR, VISIT_DATE, 103) + ']'
FROM (SELECT DISTINCT CONVERT(date, VISIT_DATE) AS VISIT_DATE FROM testing_table WHERE APPOINTMENT_TYPE = 'REG') AS Dates

-- 拼接完整SQL并执行
SET @sql = @sql + N' FROM testing_table WHERE APPOINTMENT_TYPE = ''REG'' GROUP BY LOCATION ORDER BY LOCATION'
EXEC sp_executesql @sql;

额外提示 #

• 如果VISIT_DATE本身就是字符串类型，可以去掉DATE()或CONVERT(date, ...)的转换，直接匹配字符串，但强烈建议日期字段用日期类型存储，避免格式混乱和性能问题。
• 动态SQL生成的列顺序会和日期在数据库中的存储顺序一致，如果需要按日期排序，可以在生成CASE语句时先对日期进行排序。

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