Oracle实现行转列：动态处理mapping表code列无需硬编码

Since your mapping table's code values might change daily, hardcoding column names isn't feasible. Here's how to dynamically pivot the rows into columns across popular databases:

For SQL Server #

We'll use dynamic SQL to first fetch all unique code values, then build a pivot query on the fly:

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

-- Get all distinct code values and format them as column names (column1, column2, ...)
SELECT @cols = STRING_AGG(QUOTENAME('column' + CAST(ROW_NUMBER() OVER (ORDER BY code) AS VARCHAR(10))), ', ')
FROM (SELECT DISTINCT code FROM mapping) AS codes;

-- Build the dynamic pivot query
SET @query = N'
SELECT ' + @cols + N'
FROM (
    SELECT 
        code,
        ''column'' + CAST(ROW_NUMBER() OVER (ORDER BY code) AS VARCHAR(10)) AS col_name
    FROM mapping
) AS src
PIVOT (
    MAX(code)
    FOR col_name IN (' + @cols + N')
) AS piv;';

-- Execute the query
EXEC sp_executesql @query;

This will automatically generate column1, column2, etc., matching the number of unique code values in your table, even if they change daily.

For MySQL #

MySQL doesn't have a built-in PIVOT function, so we'll use dynamic SQL with GROUP_CONCAT to generate the necessary columns:

SET @sql = NULL;

-- Generate the column list (column1, column2, ...) and the CASE statements for each column
SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'MAX(CASE WHEN rn = ', rn, ' THEN code END) AS column', rn
    )
) INTO @sql
FROM (
    SELECT code, ROW_NUMBER() OVER (ORDER BY code) AS rn
    FROM mapping
) AS ranked_codes;

-- Build and execute the full query
SET @sql = CONCAT('SELECT ', @sql, ' FROM (
    SELECT code, ROW_NUMBER() OVER (ORDER BY code) AS rn
    FROM mapping
) AS ranked_codes;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

For PostgreSQL #

You can use JSON functions for a simple, extension-free approach, or dynamic SQL for fully automatic column generation:

Using JSON Functions (No Extension Needed) #

SELECT 
    (jsonb_object_agg('column' || rn::text, code)->>'column1') AS column1,
    (jsonb_object_agg('column' || rn::text, code)->>'column2') AS column2,
    (jsonb_object_agg('column' || rn::text, code)->>'column3') AS column3,
    (jsonb_object_agg('column' || rn::text, code)->>'column4') AS column4
FROM (
    SELECT code, ROW_NUMBER() OVER (ORDER BY code) AS rn
    FROM mapping
) AS ranked_codes;

Fully Dynamic Columns (Auto-Adapts to Data Changes) #

DO $$
DECLARE
    cols text;
    query text;
BEGIN
    -- Generate column names
    SELECT string_agg('(json_data->>''column' || rn || ''') AS column' || rn, ', ')
    INTO cols
    FROM (SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY code) AS rn FROM mapping) AS r;

    -- Build the query
    query := '
    SELECT ' || cols || '
    FROM (
        SELECT jsonb_object_agg(''column'' || rn::text, code) AS json_data
        FROM (
            SELECT code, ROW_NUMBER() OVER (ORDER BY code) AS rn
            FROM mapping
        ) AS ranked_codes
    ) AS json_table;';

    -- Execute the query
    EXECUTE query;
END $$;

All these approaches avoid hardcoding the code values or column names, so they'll adapt automatically if your daily data changes (e.g., adding a new code E will create column5, removing a code will drop the corresponding column).

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