Oracle SQL Developer中转置表结构查询结果的方法

Got it, let's tackle this transpose problem in Oracle. Since you're used to Teradata's CASE approach, Oracle has a couple of solid methods to achieve this—static pivoting for fixed columns, and dynamic pivoting if you need it to work for any table without hardcoding column names.

Static Pivoting (For Known Columns) #

If you know the exact column names of your table upfront, a static PIVOT is the simplest, most straightforward way. Here's how to structure the query (adjust column names and schema as needed):

WITH col_metadata AS (
    SELECT 
        column_name, 
        data_type, 
        nullable
    FROM all_tab_columns 
    WHERE table_name = 'MYTABLE'  -- Oracle stores table names in uppercase by default
      AND owner = 'YOUR_SCHEMA'   -- Don't forget to specify your schema/username
    ORDER BY column_id ASC
)
-- First row: Data types
SELECT 
    'DATA_TYPE' AS row_type,
    "Column 1", "Column 2", "Column 3"
FROM col_metadata
PIVOT (
    MAX(data_type)  -- Aggregate function is required; MAX works since each column has one value
    FOR column_name IN ("Column 1", "Column 2", "Column 3")  -- Wrap columns with spaces in double quotes
)
UNION ALL
-- Second row: Nullability status
SELECT 
    'NULLABLE' AS row_type,
    "Column 1", "Column 2", "Column 3"
FROM col_metadata
PIVOT (
    MAX(nullable)
    FOR column_name IN ("Column 1", "Column 2", "Column 3")
);

This query will output exactly the format you want:

ROW_TYPE   | Column 1 | Column 2 | Column 3
--------------------------------------------
DATA_TYPE  | VARCHAR2 | NUMBER   | DATE
NULLABLE   | N        | Y        | N

Dynamic Pivoting (For Any Table) #

If you need a solution that works for any table without hardcoding column names (great for reusable scripts), use dynamic SQL. This will automatically generate the pivot columns based on the table's metadata:

DECLARE
    v_pivot_cols VARCHAR2(4000);
    v_sql VARCHAR2(4000);
BEGIN
    -- Generate a comma-separated list of quoted column names
    SELECT LISTAGG('"' || column_name || '"', ', ') WITHIN GROUP (ORDER BY column_id ASC)
    INTO v_pivot_cols
    FROM all_tab_columns
    WHERE table_name = 'MYTABLE'
      AND owner = 'YOUR_SCHEMA';

    -- Build the full dynamic SQL query
    v_sql := '
        WITH col_metadata AS (
            SELECT 
                column_name, 
                data_type, 
                nullable
            FROM all_tab_columns 
            WHERE table_name = ''MYTABLE'' 
              AND owner = ''YOUR_SCHEMA''
            ORDER BY column_id ASC
        )
        SELECT ''DATA_TYPE'' AS row_type, ' || v_pivot_cols || '
        FROM col_metadata
        PIVOT (MAX(data_type) FOR column_name IN (' || v_pivot_cols || '))
        UNION ALL
        SELECT ''NULLABLE'' AS row_type, ' || v_pivot_cols || '
        FROM col_metadata
        PIVOT (MAX(nullable) FOR column_name IN (' || v_pivot_cols || '))';

    -- Execute the dynamic query (works in SQL Developer's Script Output tab)
    EXECUTE IMMEDIATE v_sql;
END;
/

Key Notes #

• Always specify the owner clause to avoid pulling metadata from other users' tables with the same name.
• Oracle requires an aggregate function in PIVOT—we use MAX here because each column has exactly one data type and nullable value, so the aggregate doesn't alter the result.
• Columns with spaces or special characters must be wrapped in double quotes to match Oracle's case-sensitive identifier rules.

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