Oracle中如何动态将列名与行数据按指定格式插入另一表

Alright, let's tackle this problem. You need to take a dynamically structured table (Table1) and insert its column names along with their corresponding row values into a target table—where each column from Table1 becomes a separate row in the target. Here's how to do this in a way that adapts no matter how Table1's structure changes:

For SQL Server #

Since Table1's columns can change, we need to use dynamic SQL to dynamically fetch column names and generate the unpivot logic. This avoids hardcoding column names that might change later.

DECLARE @cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- Fetch all column names from Table1 (formatted for UNPIVOT)
SELECT @cols = STRING_AGG(QUOTENAME(name), ', ')
FROM sys.columns
WHERE object_id = OBJECT_ID('Table1');

-- Build dynamic SQL to unpivot Table1 and insert into target table
SET @sql = N'
INSERT INTO TargetTable (COL_A, COL_B)
SELECT col_name AS COL_A, col_value AS COL_B
FROM (
    -- If Table1 has multiple rows, use TOP 1 to only process the first row
    SELECT TOP 1 * FROM Table1
) AS filtered_table
UNPIVOT (
    col_value FOR col_name IN (' + @cols + ')
) AS unpvt;
';

-- Execute the dynamic SQL
EXEC sp_executesql @sql;

How this works: #

• sys.columns pulls all column names from Table1, and STRING_AGG formats them into a comma-separated list wrapped in quotes (required for UNPIVOT).
• The UNPIVOT operation transforms Table1's columns into rows: each row will have the column name (as COL_A) and its corresponding value (as COL_B).
• We filter to TOP 1 if Table1 has multiple rows (adjust this if you need to process all rows).

For MySQL #

MySQL doesn't have a native UNPIVOT function, so we'll dynamically generate UNION ALL statements to convert columns into rows:

SET @cols = NULL;

-- Generate individual SELECT statements for each column, then union them
SELECT GROUP_CONCAT(
    CONCAT('SELECT ''', column_name, ''' AS COL_A, ', column_name, ' AS COL_B FROM Table1 LIMIT 1')
    SEPARATOR ' UNION ALL '
) INTO @cols
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Table1' AND table_schema = DATABASE();

-- Build and execute the insert query
SET @sql = CONCAT('INSERT INTO TargetTable (COL_A, COL_B) ', @cols);

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

How this works: #

• INFORMATION_SCHEMA.COLUMNS fetches Table1's column names.
• We build a SELECT statement for each column that returns the column name as COL_A and its value as COL_B. UNION ALL combines these into a single result set.
• LIMIT 1 ensures we only process the first row of Table1 (remove this if you need all rows).

Key Notes #

• Target Table Setup: Make sure your target table has COL_A (string type to store column names) and COL_B (a flexible type like VARCHAR or a type matching Table1's values). The remaining columns (COL_C to COL_H) will default to NULL as shown in your example.
• Handling Multiple Rows: If Table1 has multiple rows, remove the TOP 1 (SQL Server) or LIMIT 1 (MySQL) to insert rows for every column in every row of Table1.
• Security: If Table1's column names might contain special characters or are user-controlled, add proper validation to avoid SQL injection risks.

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