SQL技术问询：如何将分组元素转置为新列（附查询结果）

Hey there! Let's figure out how to pivot those grouped reviewer names into separate columns for each director. Based on your existing query and results, here are tailored solutions depending on which database system you're using—since pivot functionality varies across SQL dialects.

First, let's recap your base query that gets the director-reviewer pairs:

SELECT DISTINCT director, name 
FROM Movie m 
JOIN Rating ra ON m.mid = ra.mid 
JOIN Reviewer re ON ra.rid = re.rid 
WHERE director IS NOT NULL 
ORDER BY director, name;

1. MySQL/MariaDB Solution #

MySQL doesn't have a native PIVOT function, so we'll use conditional aggregation with row numbering to achieve the transpose:

-- First, assign a unique rank to each reviewer per director
WITH ranked_reviews AS (
    SELECT 
        director,
        name,
        ROW_NUMBER() OVER(PARTITION BY director ORDER BY name) AS name_rank
    FROM (
        SELECT DISTINCT director, name 
        FROM Movie m 
        JOIN Rating ra ON m.mid = ra.mid 
        JOIN Reviewer re ON ra.rid = re.rid 
        WHERE director IS NOT NULL
    ) AS distinct_pairs
)
-- Pivot the ranks into separate columns
SELECT 
    director,
    MAX(CASE WHEN name_rank = 1 THEN name END) AS name_1,
    MAX(CASE WHEN name_rank = 2 THEN name END) AS name_2,
    MAX(CASE WHEN name_rank = 3 THEN name END) AS name_3 -- Add more lines if you have more reviewers per director
FROM ranked_reviews
GROUP BY director
ORDER BY director;

How this works: We first number each reviewer name under their director, then use MAX(CASE...) to pull each numbered name into its own column. If a director has fewer reviewers than the number of columns you define, the extra columns will return NULL.

2. PostgreSQL Solution #

PostgreSQL offers the crosstab function (from the tablefunc extension) for pivoting. First, enable the extension if you haven't already:

CREATE EXTENSION IF NOT EXISTS tablefunc;

Then use the crosstab query:

SELECT * FROM crosstab(
    -- Source query: returns director, rank number, and reviewer name
    'SELECT director, name_rank, name
     FROM (
         SELECT 
             director,
             name,
             ROW_NUMBER() OVER(PARTITION BY director ORDER BY name) AS name_rank
         FROM (
             SELECT DISTINCT director, name 
             FROM Movie m 
             JOIN Rating ra ON m.mid = ra.mid 
             JOIN Reviewer re ON ra.rid = re.rid 
             WHERE director IS NOT NULL
         ) AS distinct_pairs
     ) AS ranked
     ORDER BY 1, 2',
    -- Define the range of rank numbers (adjust the 3 to match max reviewers per director)
    'SELECT generate_series(1,3)'
) AS ct(director text, name_1 text, name_2 text, name_3 text)
ORDER BY director;

Alternatively, you can use the same conditional aggregation approach as MySQL—both work fine in PostgreSQL.

3. SQL Server Solution #

SQL Server has a native PIVOT operator that simplifies this task:

-- Generate column names (name_1, name_2, etc.) for each reviewer per director
WITH ranked_reviews AS (
    SELECT 
        director,
        name,
        'name_' + CAST(ROW_NUMBER() OVER(PARTITION BY director ORDER BY name) AS varchar(10)) AS name_col
    FROM (
        SELECT DISTINCT director, name 
        FROM Movie m 
        JOIN Rating ra ON m.mid = ra.mid 
        JOIN Reviewer re ON ra.rid = re.rid 
        WHERE director IS NOT NULL
    ) AS distinct_pairs
)
-- Pivot the rows into columns
SELECT director, name_1, name_2, name_3
FROM ranked_reviews
PIVOT (
    MAX(name)
    FOR name_col IN (name_1, name_2, name_3) -- Add more columns if needed
) AS pivot_table
ORDER BY director;

How this works: We first create dynamic column labels for each reviewer, then use PIVOT to rotate those labels into actual columns.

Key Notes #

• If the number of reviewers per director varies widely and you don't want to hardcode column counts, you'll need to use dynamic SQL (building the query string programmatically) to generate columns on the fly. This adds complexity but handles variable group sizes.
• Your original DISTINCT ensures no duplicate director-reviewer pairs, which is critical for accurate pivoting—duplicates would skew the row numbering and results.

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