SQL技术问询：能否用Group By多列实现Score按Section横向展示

Great question! Let's break this down straight away: You can't do this with GROUP BY alone, but combining GROUP BY with conditional aggregation (using CASE statements alongside aggregate functions like MAX or SUM) will get you exactly the pivoted table you want.

Why GROUP BY Alone Isn't Enough #

GROUP BY only groups rows together that share the same Section value—but it doesn't have a built-in way to split out scores from different TestIDs into separate columns. We need an extra layer of logic to pick out the score for each specific TestID and place it in the right column.

Solution 1: Static Conditional Aggregation (For Known TestIDs) #

If you know exactly which TestIDs you need to pivot (like TestID 1 and 2 here, with room for a 3rd), this straightforward query will work:

SELECT
  Section,
  MAX(CASE WHEN TestID = 1 THEN Score END) AS Score1,
  MAX(CASE WHEN TestID = 2 THEN Score END) AS Score2,
  MAX(CASE WHEN TestID = 3 THEN Score END) AS Score3 -- Add this if you need to support up to 3 tests
FROM your_table_name
GROUP BY Section
ORDER BY Section;

How This Works:

• GROUP BY Section clusters all rows for each section together.
• Each CASE statement checks if the row's TestID matches the target, returning the score if true, or NULL otherwise.
• MAX() ignores the NULL values and grabs the single valid score for each TestID in the group—perfect since each Section + TestID pair only has one score.

Solution 2: Flexible Pivoting (For Up to 3 Tests, Even With Unordered TestIDs) #

If your TestIDs might not be sequential, but you still want the first 3 scores per section (ordered by TestID), use a window function to rank scores first, then pivot:

WITH ranked_scores AS (
  SELECT
    Section,
    Score,
    -- Assign a rank to each score in the section, ordered by TestID
    ROW_NUMBER() OVER (PARTITION BY Section ORDER BY TestID) AS score_rank
  FROM your_table_name
)
SELECT
  Section,
  MAX(CASE WHEN score_rank = 1 THEN Score END) AS Score1,
  MAX(CASE WHEN score_rank = 2 THEN Score END) AS Score2,
  MAX(CASE WHEN score_rank = 3 THEN Score END) AS Score3
FROM ranked_scores
GROUP BY Section
ORDER BY Section;

This approach works even if TestIDs are skipped or out of order, as long as you want the first 3 scores sorted by TestID.

Final Result #

Either query will output exactly the table you're looking for:

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