SQL Server调查响应数据透视表实现求助：单模块报表需求

Hey Richard, let's work through this together! Since you've identified UNPIVOT and PIVOT as the right tools for generating per-module reports from your surveys table, I'll walk you through practical examples you can adapt to your specific data structure and desired output.

第一步：先明确常见的表结构场景 #

Let's start with a typical surveys table setup (adjust this to match your actual schema):

CREATE TABLE surveys (
    SurveyID INT,
    ModuleName VARCHAR(50), -- 模块名称，你要按这个分组生成报表
    Question1 INT, -- 问题1的得分/结果
    Question2 INT, -- 问题2的得分/结果
    Question3 INT, -- 问题3的得分/结果
    RespondentCount INT -- 该模块的受访者数量
);

第二步：用UNPIVOT把宽表转成窄表 #

First, we'll use UNPIVOT to transform column-based questions into row-based entries—this makes it easier to aggregate or restructure data for your report:

SELECT 
    SurveyID,
    ModuleName,
    Question, -- 原来的Question1/2/3会变成这里的行值
    Score, -- 对应问题的得分
    RespondentCount
FROM surveys
UNPIVOT (
    -- 把Question1/2/3的值映射到Score字段
    Score FOR Question IN (Question1, Question2, Question3)
) AS unpivoted_survey_data;

This query will turn each question column into a separate row, so you'll have one row per (SurveyID, ModuleName, Question) combination.

第三步：用PIVOT生成最终报表格式 #

Now we can take the unpivoted data and use PIVOT to restructure it into your desired report layout. For example, if you want a report that shows average scores per question, grouped by module:

SELECT 
    ModuleName,
    Question1, -- 转回到列的问题1平均得分
    Question2,
    Question3,
    TotalRespondents -- 该模块的总受访者数
FROM (
    -- 先做UNPIVOT并计算模块总受访者数
    SELECT 
        ModuleName,
        Question,
        Score,
        SUM(RespondentCount) OVER (PARTITION BY ModuleName) AS TotalRespondents
    FROM surveys
    UNPIVOT (
        Score FOR Question IN (Question1, Question2, Question3)
    ) AS unpivoted_data
) AS pre_pivot_data
PIVOT (
    -- 这里用AVG计算平均得分，你可以换成SUM/MAX等其他聚合函数
    AVG(Score)
    FOR Question IN (Question1, Question2, Question3)
) AS module_survey_report;

另一种场景：统计选项分布 #

If your surveys table stores individual respondent answers (like "Agree"/"Disagree" instead of scores), here's how you'd adjust the logic:

-- 示例表结构
CREATE TABLE surveys (
    RespondentID INT,
    ModuleName VARCHAR(50),
    Q1_Answer VARCHAR(20),
    Q2_Answer VARCHAR(20)
);

-- 生成按模块、问题统计选项数量的报表
SELECT 
    ModuleName,
    Question,
    [Agree],
    [Disagree],
    [Neutral]
FROM (
    SELECT 
        ModuleName,
        Question,
        Answer
    FROM surveys
    UNPIVOT (
        Answer FOR Question IN (Q1_Answer, Q2_Answer)
    ) AS unpivoted_responses
) AS pre_pivot_data
PIVOT (
    COUNT(Answer) -- 统计每个选项的受访者数量
    FOR Answer IN ([Agree], [Disagree], [Neutral])
) AS module_response_report;

适配你的实际数据 #

If your table has different columns (e.g., more questions, different metrics like completion rates) or your desired report has a specific layout, just tweak:

• The columns listed in the UNPIVOT clause to match your question/metric columns
• The aggregation function in PIVOT (AVG/SUM/COUNT/etc.) to fit your reporting needs
• The values in the FOR ... IN clause of PIVOT to match your answer options or question names

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