SQL查询动态添加列实现：参会者活动场次预订统计需求

嗨，我完全懂你现在的痛点——每次新增场次都要手动修改SQL加列，太繁琐了！咱们一步步解决这个问题，既要实现动态筛选活动场次，还要自动生成对应的场次列，彻底告别手动改代码的麻烦。

问题背景 #

你需要处理活动（event）中参会者（delegate）的场次（session）预订数据，核心需求是：

1. 自动列出某一活动下的所有场次，不用手动指定场次ID
2. 动态生成场次对应的列，查看每位参会者的预订情况，适配不同活动的场次数量差异

你之前试过静态PIVOT觉得不适用，那是因为静态PIVOT需要提前固定列名，而动态场景下必须用动态SQL+动态PIVOT才能实现。

你的现有代码 #

先把你提供的内容整理成清晰的格式：

建表及数据插入SQL #

CREATE TABLE delegate( 
    DELEGATE_REF INTEGER NOT NULL PRIMARY KEY ,
    code INTEGER NOT NULL ,
    name VARCHAR(10) NOT NULL ,
    MEMBER_REF INTEGER NOT NULL ,
    TOTAL_AMOUNT INTEGER NOT NULL ,
    DELEGATE_SESS_REF INTEGER NOT NULL ,
    EVENT_REF INTEGER NOT NULL 
);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES 
(26174,51,'Delegate A',1077419,280,58136,378),
(26183,52,'Delegate B',1110544,302,58157,378),
(26206,53,'Delegate C',1084626,169,58209,378),
(26210,54,'Delegate D',1092456,257,58218,378),
(26212,55,'Delegate E',1055867,221,58223,378),
(26220,56,'Delegate F',1109833,169,58240,378),
(26229,57,'Delegate G',266050,0,58258,378),
(26230,58,'Delegate H',1110868,0,58260,378),
(26231,59,'Delegate I',1110890,0,58262,378),
(26232,60,'Delegate J',1110891,0,58264,378);

CREATE TABLE event( 
    code VARCHAR(6) NOT NULL ,
    event_ref INTEGER NOT NULL PRIMARY KEY ,
    name VARCHAR(12) NOT NULL 
);
INSERT INTO event(code,event_ref,name) VALUES ('AC2017',378,'MyConference');

CREATE TABLE delegate_session( 
    DELEGATE_REF INTEGER NOT NULL ,
    DELEGATE_SESS_REF INTEGER NOT NULL PRIMARY KEY ,
    SESSION_REF INTEGER NOT NULL 
);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF) VALUES 
(26183,58157,460),(26206,58209,460),(26212,58223,460),(26220,58240,460),
(26229,58258,460),(26230,58260,460),(26231,58262,460),(26232,58264,460),
(26174,58136,460),(26210,58218,460);

CREATE TABLE session( 
    SESSION_REF INTEGER NOT NULL PRIMARY KEY ,
    NAME VARCHAR(16) NOT NULL 
);
INSERT INTO session(SESSION_REF,NAME) VALUES (460,'Delegate booking');

当前手动查询SQL #

select e.NAME, d.code, d.name, d.MEMBER_REF, d.TOTAL_AMOUNT, x1.t1 as 'Session 1'
from DELEGATE as d
INNER JOIN EVENT as e on d.EVENT_REF=e.EVENT_REF
left join (
    select d.DELEGATE_REF, s.name as 't1'
    FROM DELEGATE as d
    INNER JOIN DELEGATE_SESSION as ds on d.DELEGATE_REF=ds.DELEGATE_REF
    INNER JOIN SESSION as s on ds.SESSION_REF=s.SESSION_REF
    where s.SESSION_REF=460
) as x1 on d.DELEGATE_REF=x1.delegate_ref
where d.code > 50 and d.code < 61 and e.code like 'ac2017'
group by e.NAME, d.code, d.name, d.MEMBER_REF, d.TOTAL_AMOUNT, x1.t1

解决方案 #

1. 动态筛选活动场次（无需手动指定SESSION_REF） #

如果只是想自动筛选某活动下的所有场次，不用硬编码SESSION_REF=460，可以用子查询获取该活动对应的所有场次ID，再用IN子句关联：

select 
    e.NAME, 
    d.code, 
    d.name, 
    d.MEMBER_REF, 
    d.TOTAL_AMOUNT, 
    s.name as 'Session Name'
from DELEGATE as d
INNER JOIN EVENT as e on d.EVENT_REF=e.EVENT_REF
LEFT JOIN DELEGATE_SESSION as ds on d.DELEGATE_REF=ds.DELEGATE_REF
LEFT JOIN SESSION as s on ds.SESSION_REF=s.SESSION_REF
where 
    d.code > 50 and d.code < 61 
    and e.code like 'ac2017'
    -- 动态筛选该活动下的所有场次
    and s.SESSION_REF IN (
        SELECT DISTINCT s.SESSION_REF
        FROM EVENT e
        JOIN DELEGATE d ON e.EVENT_REF = d.EVENT_REF
        JOIN DELEGATE_SESSION ds ON d.DELEGATE_REF = ds.DELEGATE_REF
        JOIN SESSION s ON ds.SESSION_REF = s.SESSION_REF
        WHERE e.code = 'ac2017'
    )
group by e.NAME, d.code, d.name, d.MEMBER_REF, d.TOTAL_AMOUNT, s.name

这样不管活动新增多少场次，都会自动包含进去，不用手动修改WHERE条件。

2. 动态生成场次列（核心解决方案） #

如果要实现你期望的「每个场次作为单独列展示」的效果，就需要用动态SQL+动态PIVOT。下面是适配任意场次数量的代码：

DECLARE @eventCode VARCHAR(6) = 'AC2017'; -- 指定要查询的活动编码
DECLARE @cols NVARCHAR(MAX); -- 存储动态生成的场次列名
DECLARE @sql NVARCHAR(MAX); -- 存储最终执行的动态SQL

-- 步骤1：获取该活动下的所有场次名称，拼接成PIVOT需要的列名格式（比如[Delegate booking]）
-- SQL Server 2017+用STRING_AGG更简洁
SELECT @cols = STRING_AGG(QUOTENAME(s.NAME), ', ')
FROM EVENT e
JOIN DELEGATE d ON e.EVENT_REF = d.EVENT_REF
JOIN DELEGATE_SESSION ds ON d.DELEGATE_REF = ds.DELEGATE_REF
JOIN SESSION s ON ds.SESSION_REF = s.SESSION_REF
WHERE e.code = @eventCode
GROUP BY s.SESSION_REF, s.NAME;

-- 若为SQL Server 2016及更早版本，用STUFF+FOR XML PATH替代STRING_AGG
/*
SELECT @cols = STUFF((SELECT ', ' + QUOTENAME(s.NAME)
                      FROM EVENT e
                      JOIN DELEGATE d ON e.EVENT_REF = d.EVENT_REF
                      JOIN DELEGATE_SESSION ds ON d.DELEGATE_REF = ds.DELEGATE_REF
                      JOIN SESSION s ON ds.SESSION_REF = s.SESSION_REF
                      WHERE e.code = @eventCode
                      GROUP BY s.SESSION_REF, s.NAME
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');
*/

-- 步骤2：拼接动态SQL，使用PIVOT转置场次为列
SET @sql = N'
SELECT 
    e.NAME AS ''NAME'',
    d.code,
    d.name,
    d.MEMBER_REF,
    d.TOTAL_AMOUNT,
    ' + @cols + N',
    1 AS ''Number'', -- 假设每场预订票数为1，如需统计实际票数可调整PIVOT内的聚合函数
    ''Standard'' AS ''Rate'' -- 若Rate来自其他表，替换为对应字段即可
FROM DELEGATE d
INNER JOIN EVENT e ON d.EVENT_REF = e.EVENT_REF
LEFT JOIN DELEGATE_SESSION ds ON d.DELEGATE_REF = ds.DELEGATE_REF
LEFT JOIN SESSION s ON ds.SESSION_REF = s.SESSION_REF
WHERE d.code > 50 AND d.code < 61 AND e.code = ''' + @eventCode + '''
PIVOT (
    MAX(s.NAME) -- 用MAX显示场次名称，如需显示票数可改为COUNT(*)
    FOR s.NAME IN (' + @cols + N')
) AS pvt
GROUP BY e.NAME, d.code, d.name, d.MEMBER_REF, d.TOTAL_AMOUNT;
';

-- 步骤3：执行动态SQL
EXEC sp_executesql @sql;

代码说明：

• 先通过子查询获取指定活动下的所有场次名称，格式化为[场次名称]的形式并拼接成字符串，作为PIVOT的列列表。
• 再拼接完整的SQL语句，用PIVOT实现行转列，把每个场次变成单独的列。
• 最后执行动态SQL，自动生成对应活动的所有场次列，完全不用手动修改。

如果需要把场次命名为Session 1、Session 2这样的序号格式，只需修改获取列名的部分：

SELECT @cols = STRING_AGG(QUOTENAME('Session ' + CAST(ROW_NUMBER() OVER(ORDER BY s.SESSION_REF) AS VARCHAR)), ', ')
FROM EVENT e
JOIN DELEGATE d ON e.EVENT_REF = d.EVENT_REF
JOIN DELEGATE_SESSION ds ON d.DELEGATE_REF = ds.DELEGATE_REF
JOIN SESSION s ON ds.SESSION_REF = s.SESSION_REF
WHERE e.code = @eventCode
GROUP BY s.SESSION_REF, s.NAME;

最终效果 #

执行上述动态SQL后，就能得到你期望的输出格式：

不管活动新增多少场次，代码都能自动适配，彻底告别手动加列的烦恼！

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