如何在SQL Server中创建透视表？基于Table_Marks表的格式转换需求

Got it, let's work through transforming your Table_Marks data into the pivot table format you need. First, let's recap your source data clearly:

Original Table: Table_Marks #

Your goal is to have one row per student (grouped by CLASS, STD, NAME) with columns for each subject's scores across PT1, PTB1, PT2, PTB2, plus calculated totals (T1 for PT1+PTB1, T2 for PT2+PTB2). Let's break down the solutions:

Solution 1: Universal SQL (Works for all databases) #

This uses CASE WHEN with aggregate functions to pivot the data, and handles the 'AB' (absent) values by keeping the 'AB' label if either component score is absent.

SELECT
    CLASS,
    STD,
    NAME,
    -- English scores (PT1, PTB1, T1)
    MAX(CASE WHEN SUBJECT = 'ENG' THEN PT1_M END) AS ENG_PT1,
    MAX(CASE WHEN SUBJECT = 'ENG' THEN PTB1_M END) AS ENG_PTB1,
    MAX(CASE WHEN SUBJECT = 'ENG' THEN 
        CASE WHEN PT1_M = 'AB' OR PTB1_M = 'AB' THEN 'AB' 
             ELSE CAST(CAST(PT1_M AS INT) + CAST(PTB1_M AS INT) AS VARCHAR) 
        END 
    END) AS ENG_T1,
    -- Hindi scores (PT1, PTB1, T1)
    MAX(CASE WHEN SUBJECT = 'HIN' THEN PT1_M END) AS HIN_PT1,
    MAX(CASE WHEN SUBJECT = 'HIN' THEN PTB1_M END) AS HIN_PTB1,
    MAX(CASE WHEN SUBJECT = 'HIN' THEN 
        CASE WHEN PT1_M = 'AB' OR PTB1_M = 'AB' THEN 'AB' 
             ELSE CAST(CAST(PT1_M AS INT) + CAST(PTB1_M AS INT) AS VARCHAR) 
        END 
    END) AS HIN_T1,
    -- Math scores (PT1, PTB1, T1)
    MAX(CASE WHEN SUBJECT = 'MATH' THEN PT1_M END) AS MATH_PT1,
    MAX(CASE WHEN SUBJECT = 'MATH' THEN PTB1_M END) AS MATH_PTB1,
    MAX(CASE WHEN SUBJECT = 'MATH' THEN 
        CASE WHEN PT1_M = 'AB' OR PTB1_M = 'AB' THEN 'AB' 
             ELSE CAST(CAST(PT1_M AS INT) + CAST(PTB1_M AS INT) AS VARCHAR) 
        END 
    END) AS MATH_T1,
    -- English scores (PT2, PTB2, T2)
    MAX(CASE WHEN SUBJECT = 'ENG' THEN PT2_M END) AS ENG_PT2,
    MAX(CASE WHEN SUBJECT = 'ENG' THEN PTB2_M END) AS ENG_PTB2,
    MAX(CASE WHEN SUBJECT = 'ENG' THEN 
        CASE WHEN PT2_M = 'AB' OR PTB2_M = 'AB' THEN 'AB' 
             ELSE CAST(CAST(PT2_M AS INT) + CAST(PTB2_M AS INT) AS VARCHAR) 
        END 
    END) AS ENG_T2,
    -- Hindi scores (PT2, PTB2, T2)
    MAX(CASE WHEN SUBJECT = 'HIN' THEN PT2_M END) AS HIN_PT2,
    MAX(CASE WHEN SUBJECT = 'HIN' THEN PTB2_M END) AS HIN_PTB2,
    MAX(CASE WHEN SUBJECT = 'HIN' THEN 
        CASE WHEN PT2_M = 'AB' OR PTB2_M = 'AB' THEN 'AB' 
             ELSE CAST(CAST(PT2_M AS INT) + CAST(PTB2_M AS INT) AS VARCHAR) 
        END 
    END) AS HIN_T2,
    -- Math scores (PT2, PTB2, T2)
    MAX(CASE WHEN SUBJECT = 'MATH' THEN PT2_M END) AS MATH_PT2,
    MAX(CASE WHEN SUBJECT = 'MATH' THEN PTB2_M END) AS MATH_PTB2,
    MAX(CASE WHEN SUBJECT = 'MATH' THEN 
        CASE WHEN PT2_M = 'AB' OR PTB2_M = 'AB' THEN 'AB' 
             ELSE CAST(CAST(PT2_M AS INT) + CAST(PTB2_M AS INT) AS VARCHAR) 
        END 
    END) AS MATH_T2
FROM Table_Marks
GROUP BY CLASS, STD, NAME
ORDER BY CLASS, STD, NAME;

Solution 2: SQL Server-Specific (Using PIVOT/UNPIVOT) #

If you're using SQL Server, this approach is more scalable for larger datasets with more subjects or test types. It first unpivots the score columns, calculates totals, then pivots back to the desired format.

-- Step 1: Unpivot score columns to get one row per score type
WITH Unpivoted AS (
    SELECT
        CLASS,
        STD,
        NAME,
        SUBJECT + '_' + ScoreType AS ScoreColumn,
        ScoreValue
    FROM Table_Marks
    UNPIVOT (
        ScoreValue FOR ScoreType IN (PT1_M, PTB1_M, PT2_M, PTB2_M)
    ) AS up
),
-- Step 2: Prepare data for total score calculation
CalculatedScores AS (
    SELECT
        CLASS,
        STD,
        NAME,
        ScoreColumn,
        ScoreValue,
        -- Define which total column each score belongs to (T1 or T2)
        CASE 
            WHEN ScoreColumn LIKE '%_PT1_M' OR ScoreColumn LIKE '%_PTB1_M' THEN SUBSTRING(ScoreColumn, 1, CHARINDEX('_', ScoreColumn)-1) + '_T1'
            WHEN ScoreColumn LIKE '%_PT2_M' OR ScoreColumn LIKE '%_PTB2_M' THEN SUBSTRING(ScoreColumn, 1, CHARINDEX('_', ScoreColumn)-1) + '_T2'
        END AS TotalColumn,
        -- Convert scores to numeric (treat AB as 0 for calculation)
        CASE WHEN ScoreValue = 'AB' THEN 0 ELSE CAST(ScoreValue AS INT) END AS NumericScore
    FROM Unpivoted
),
-- Step 3: Calculate total scores (T1 and T2)
TotalScores AS (
    SELECT
        CLASS,
        STD,
        NAME,
        TotalColumn,
        -- Show AB if any component was AB, else show sum
        CASE WHEN SUM(CASE WHEN ScoreValue = 'AB' THEN 1 ELSE 0 END) > 0 THEN 'AB' ELSE CAST(SUM(NumericScore) AS VARCHAR) END AS TotalValue
    FROM CalculatedScores
    GROUP BY CLASS, STD, NAME, TotalColumn
)
-- Step 4: Combine original scores and totals, then pivot to final format
SELECT *
FROM (
    SELECT CLASS, STD, NAME, ScoreColumn, ScoreValue FROM Unpivoted
    UNION ALL
    SELECT CLASS, STD, NAME, TotalColumn, TotalValue FROM TotalScores
) AS CombinedData
PIVOT (
    MAX(ScoreValue) FOR ScoreColumn IN (
        ENG_PT1_M, ENG_PTB1_M, ENG_T1,
        HIN_PT1_M, HIN_PTB1_M, HIN_T1,
        MATH_PT1_M, MATH_PTB1_M, MATH_T1,
        ENG_PT2_M, ENG_PTB2_M, ENG_T2,
        HIN_PT2_M, HIN_PTB2_M, HIN_T2,
        MATH_PT2_M, MATH_PTB2_M, MATH_T2
    )
) AS FinalPivot
ORDER BY CLASS, STD, NAME;

Final Pivot Table Result #

Running either query will give you this formatted output:

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