如何在PostgreSQL中基于多行数据动态创建透视表实现行转列

Hey there! Let's tackle this problem where you need to pivot multiple rows per user (uId) into a single row with dynamically generated columns (like location1, rate1, etc., based on how many entries each user has). Here's how to get your desired output:

Step 1: Understand the Core Approach #

PostgreSQL doesn't have a built-in dynamic pivot function, but we can achieve this using dynamic SQL combined with window functions to number each user's rows. The key steps are:

• Assign a unique row number to each entry per user (to map rows to columns).
• Dynamically generate SQL to create pivot columns for the maximum number of rows any user has.
• Execute the dynamic SQL to get the pivoted result.

Step 2: Full Solution with PL/pgSQL Function #

This reusable function will automatically adjust to the number of rows per user:

CREATE OR REPLACE FUNCTION pivot_user_data()
RETURNS TABLE (
    uId INT,
    fnme TEXT,
    mnme TEXT,
    lnme TEXT
) AS $$
DECLARE
    max_rows_per_user INT;
    dynamic_columns TEXT;
    pivot_select_clause TEXT;
BEGIN
    -- First, find the maximum number of rows any single user has
    SELECT MAX(row_count) INTO max_rows_per_user
    FROM (SELECT COUNT(*) AS row_count FROM your_table GROUP BY uId) user_row_counts;

    -- Generate definitions for dynamic columns (locationN, rateN, hoursN)
    dynamic_columns := '';
    FOR i IN 1..max_rows_per_user LOOP
        dynamic_columns := dynamic_columns || format(',
            location%s TEXT,
            rate%s NUMERIC,
            hours%s NUMERIC', i, i, i);
    END LOOP;

    -- Update the function's return type to include dynamic columns
    EXECUTE format('ALTER FUNCTION pivot_user_data() RETURNS TABLE (uId INT, fnme TEXT, mnme TEXT, lnme TEXT %s)', dynamic_columns);

    -- Build the SELECT clause for pivoting using CASE statements
    pivot_select_clause := 'uId, fnme, mnme, lnme';
    FOR i IN 1..max_rows_per_user LOOP
        pivot_select_clause := pivot_select_clause || format(',
            MAX(CASE WHEN row_num = %s THEN location END) AS location%s,
            MAX(CASE WHEN row_num = %s THEN rate END) AS rate%s,
            MAX(CASE WHEN row_num = %s THEN hours END) AS hours%s',
            i, i, i, i, i, i);
    END LOOP;

    -- Execute the dynamic pivot query and return results
    RETURN QUERY EXECUTE format('
        SELECT %s
        FROM (
            SELECT 
                uId, fnme, mnme, lnme,
                location, rate, hours,
                -- Assign row number to each entry per user (ordered by date)
                ROW_NUMBER() OVER (PARTITION BY uId ORDER BY date) AS row_num
            FROM your_table
        ) numbered_rows
        GROUP BY uId, fnme, mnme, lnme
        ORDER BY uId', pivot_select_clause);
END;
$$ LANGUAGE plpgsql;

Step 3: Use the Function #

Call the function to get your pivoted table:

SELECT * FROM pivot_user_data();

Key Notes #

• Order of Columns: The ORDER BY date in the ROW_NUMBER() clause determines which entry maps to location1, location2, etc. Adjust this if you want a different order (e.g., ORDER BY hours DESC).
• Handling NULLs: If a user has fewer entries than the maximum row count, the extra columns will return NULL (matches your example for user 2).
• Reusability: The function will automatically adjust if you add more rows for existing users or new users in the future.

Alternative: One-Time Dynamic SQL (No Function) #

If you don't want to create a function, you can run this one-time dynamic query (works in psql with \gexec or in a PL/pgSQL block):

WITH user_row_counts AS (
    SELECT COUNT(*) AS row_count FROM your_table GROUP BY uId
), max_rows AS (
    SELECT MAX(row_count) AS max_r FROM user_row_counts
), pivot_columns AS (
    SELECT string_agg(
        format(',
            MAX(CASE WHEN row_num = %s THEN location END) AS location%s,
            MAX(CASE WHEN row_num = %s THEN rate END) AS rate%s,
            MAX(CASE WHEN row_num = %s THEN hours END) AS hours%s',
            i, i, i, i, i, i),
        ''
    ) AS cols
    FROM generate_series(1, (SELECT max_r FROM max_rows)) i
)
SELECT format('
    SELECT uId, fnme, mnme, lnme %s
    FROM (
        SELECT 
            uId, fnme, mnme, lnme,
            location, rate, hours,
            ROW_NUMBER() OVER (PARTITION BY uId ORDER BY date) AS row_num
        FROM your_table
    ) numbered_rows
    GROUP BY uId, fnme, mnme, lnme
    ORDER BY uId', (SELECT cols FROM pivot_columns)) AS dynamic_sql \gexec;

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