SSRS矩阵（含静态与动态列）：动态课程数据透视需求

嘿，看来你需要把动态变化的Courses字段做透视转换，让数据按Name和ID聚合展示对吧？我给你整理了几种主流数据库的实现方案，包括能自动适配课程新增/移除的动态方法，完全对应你的示例数据来写的：

1. SQL Server 实现（使用PIVOT） #

静态透视（适用于已知课程） #

如果课程是固定的，可以直接写静态查询：

SELECT Name, ID, math, Chemistry
FROM (
    SELECT Name, ID, Courses, Taken
    FROM YourTableName -- 替换成你的实际表名
) AS SourceTable
PIVOT (
    MAX(Taken) FOR Courses IN (math, Chemistry) -- 列出来所有已知课程
) AS PivotTable;

动态透视（自动适配课程新增/移除） #

因为Courses是动态字段，静态查询没法处理新增的课程，所以用动态SQL自动生成列：

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

-- 自动获取所有不同的课程名称，拼接成列名
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(Courses)
                    FROM YourTableName
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

-- 生成完整的透视查询语句
SET @query = 'SELECT Name, ID, ' + @cols + ' 
            FROM (
                SELECT Name, ID, Courses, Taken
                FROM YourTableName
            ) AS SourceTable
            PIVOT (
                MAX(Taken) FOR Courses IN (' + @cols + ')
            ) AS PivotTable';

-- 执行动态查询
EXECUTE(@query);

说明：这里用MAX(Taken)是因为每个Name+ID+Courses组合只有一条记录，用MAX/MIN都能正确获取Taken的值

2. MySQL 实现（使用CASE + GROUP BY） #

MySQL没有原生的PIVOT函数，所以用CASE语句结合GROUP BY来实现：

静态透视 #

SELECT 
    Name,
    ID,
    MAX(CASE WHEN Courses = 'math' THEN Taken END) AS math,
    MAX(CASE WHEN Courses = 'Chemistry' THEN Taken END) AS Chemistry
FROM YourTableName
GROUP BY Name, ID;

动态透视（自动适配课程变化） #

SET @sql = NULL;

-- 拼接所有课程对应的CASE语句
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN Courses = ''',
      Courses,
      ''' THEN Taken END) AS ',
      Courses
    )
  ) INTO @sql
FROM YourTableName;

-- 生成完整查询语句
SET @sql = CONCAT('SELECT Name, ID, ', @sql, ' 
                  FROM YourTableName 
                  GROUP BY Name, ID');

-- 预处理并执行查询
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

注意：如果课程名称包含特殊字符，可能需要用QUOTE()函数来转义，避免语法错误

3. PostgreSQL 实现（使用crosstab函数） #

PostgreSQL需要先启用tablefunc扩展，然后用crosstab函数实现透视：

第一步：启用扩展 #

CREATE EXTENSION IF NOT EXISTS tablefunc;

静态透视 #

SELECT *
FROM crosstab(
    -- 源数据查询，必须按Name、ID排序
    'SELECT Name, ID, Courses, Taken FROM YourTableName ORDER BY 1,2',
    -- 课程列表查询，定义透视后的列顺序
    'SELECT DISTINCT Courses FROM YourTableName ORDER BY 1'
) AS ct(Name text, ID int, math text, Chemistry text); -- 对应课程列的类型

动态透视（自动适配课程变化） #

WITH courses AS (
    -- 获取所有不同的课程名称
    SELECT DISTINCT Courses FROM YourTableName ORDER BY Courses
),
col_definitions AS (
    -- 拼接列定义（比如"math text, Chemistry text"）
    SELECT string_agg(Courses || ' text', ', ') AS col_def
    FROM courses
)
-- 生成动态的crosstab查询语句
SELECT format(
    'SELECT * FROM crosstab(
        ''SELECT Name, ID, Courses, Taken FROM YourTableName ORDER BY 1,2'',
        ''SELECT DISTINCT Courses FROM YourTableName ORDER BY 1''
    ) AS ct(Name text, ID int, %s);',
    col_def
) INTO @sql
FROM col_definitions;

-- 执行动态查询
EXECUTE @sql;

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