Is there a way to query a table such that the name of one of the columns being retrieved derived from a query.

For example

-- TABLE STUDENTS: STUDENT_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME

CREATE TABLE STUDENTS
(
    STUDENT_ID  NUMBER, 
    FIRST_NAME  VARCHAR2(150 BYTE), 
    MIDDLE_NAME VARCHAR2(150 BYTE),
    LAST_NAME   VARCHAR2(150 BYTE) 
); 

INSERT INTO students 
VALUES (123, 'Joe', 'F', 'Blow'); 

I want to get this output:

123 FIRST Joe
123 MIDDLE F
123 LAST Blow

I think something like this should work but it does not. The third column is the problem

SELECT 
    student_id, which_name, A.which_name||'_name'
FROM
    students A,
    (SELECT SUBSTR(column_name, 1, INSTR(column_name, '_') - 1) WHICH_NAME
     FROM all_tab_columns
     WHERE table_name = 'STUDENTS'
       AND column_name like '%_NAME') B;

I know I could do it with a CASE expression. But that would fail if additional _name columns were added eg. maiden_name, sur_name, ...

Is there a way to query a table such that the name of one of the columns being retrieved derived from a query.

For example

-- TABLE STUDENTS: STUDENT_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME

CREATE TABLE STUDENTS
(
    STUDENT_ID  NUMBER, 
    FIRST_NAME  VARCHAR2(150 BYTE), 
    MIDDLE_NAME VARCHAR2(150 BYTE),
    LAST_NAME   VARCHAR2(150 BYTE) 
); 

INSERT INTO students 
VALUES (123, 'Joe', 'F', 'Blow'); 

I want to get this output:

123 FIRST Joe
123 MIDDLE F
123 LAST Blow

I think something like this should work but it does not. The third column is the problem

SELECT 
    student_id, which_name, A.which_name||'_name'
FROM
    students A,
    (SELECT SUBSTR(column_name, 1, INSTR(column_name, '_') - 1) WHICH_NAME
     FROM all_tab_columns
     WHERE table_name = 'STUDENTS'
       AND column_name like '%_NAME') B;

I know I could do it with a CASE expression. But that would fail if additional _name columns were added eg. maiden_name, sur_name, ...

• sql
• oracle-database

• What you want can be done using the UNPIVOT operator – Panagiotis Kanavos Commented Jan 31 at 15:30
• EXECUTE IMMEDIATE can be used to create a string at runtime, then run it as a statement. – Randy Commented Jan 31 at 16:38
• 1 Yes, this is a typical case for a Dynamic SQL solution. Just write a stored procedure in two steps: step #1, compute the list of columns; step #2, assemble the dynamic query and execute it. – The Impaler Commented Jan 31 at 16:56

4 Answers 4

Based on what is mentioned in comments, I approached with a dynamic SQL

You can generate the unpivot query like below

Fiddle

DECLARE
  v_sql VARCHAR2(4000);
  v_column_names VARCHAR2(4000);
BEGIN
  SELECT LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID)
  INTO v_column_names
  FROM USER_TAB_COLUMNS
  WHERE TABLE_NAME = 'STUDENTS'
  AND COLUMN_NAME != 'STUDENT_ID';

  v_sql := 'SELECT STUDENT_ID, COLUMN_NAME, VALUE ' ||
           'FROM STUDENTS ' ||
           'UNPIVOT (VALUE FOR COLUMN_NAME IN (' || v_column_names || ')) ';

  DBMS_OUTPUT.PUT_LINE(v_sql);  
  -- EXECUTE IMMEDIATE v_sql;

END;
/

which generates

SELECT STUDENT_ID, COLUMN_NAME, VALUE FROM 
  STUDENTS UNPIVOT
  (VALUE FOR COLUMN_NAME IN (FIRST_NAME, MIDDLE_NAME, LAST_NAME))  ;

Why not :

SELECT student_id, 'FIRST NAME', FIRST_NAME  
FROM   students A
UNION ALL
SELECT student_id, 'MIDDEL NAME', MIDDLE_NAME 
FROM   students A
UNION ALL
SELECT student_id, 'LAST NAME',  LAST_NAME 
FROM   students A

Adding to Samhita's answer, the below version packages everything together into a single function. The packaging is not simple though, as you must create multiple types and the function has a few complications to avoid performance issues.

This function recreates what IDEs often call a "single record view". This kind of dynamic SQL is generally not a best practice. Unless you have some very specific use case, you are likely better off just using regular, static SQL statements.

-- A record to hold the three student values:
create or replace type student_single_record_view_type is object
(
    student_id  number,
    column_name varchar2(128),
    value       varchar2(4000)
);


-- A nested table of the three student values.
create or replace type student_single_record_view_nt is table of student_single_record_view_type;


-- A function that retrieves and returns the students table as a single record view.
create or replace function get_student_single_record_view
return student_single_record_view_type_nt pipelined is
    v_sql VARCHAR2(4000);
    v_column_names VARCHAR2(4000);
    v_results student_single_record_view_nt := student_single_record_view_nt();
    v_cur sys_refcursor;
begin
    -- Get the column names for the UNPIVOT.
    select listagg(column_name, ', ') within group (order by column_id)
    into v_column_names
    from user_tab_columns
    where table_name = 'STUDENTS'
    and column_name != 'STUDENT_ID';

    -- Build a SQL statement to retrieve all the data.
    -- Note: You may need some extra processing to handle date formatting.
    -- (Remove the "INCLUDE NULLS" if you want to exclude nulls.)
    v_sql := 'SELECT STUDENT_SINGLE_RECORD_VIEW_TYPE(STUDENT_ID, COLUMN_NAME, VALUE) ' ||
             'FROM STUDENTS ' ||
             'UNPIVOT INCLUDE NULLS (VALUE FOR COLUMN_NAME IN (' || v_column_names || ')) ';

    -- Uncomment this for debugging the SQL:
    --dbms_output.put_line(v_sql);

    -- Open a dynamic cursor for the SQL.
    open v_cur for v_sql;

    -- Fetch and pipe the results 1000 at a time.
    -- (The 1000 improves performance compared to row-by-row, and
    --  avoids possible memory issues of retrieving them all at once.)
    loop
        fetch v_cur
        bulk collect into v_results
        limit 1000;

        for i in 1 .. v_results.count loop
            pipe row (v_results(i));
        end loop;

        exit when v_results.count = 0;
    end loop;
end;
/

Here is an example of querying the function:

select *
from table(get_student_single_record_view);


STUDENT_ID  COLUMN_NAME  VALUE
----------  -----------  -----
123         FIRST_NAME   Joe
123         MIDDLE_NAME  F
123         LAST_NAME    Blow

What you ask can be done with the UNPIVOT operator :

select * from students
unpivot (
  value for field_name in (
    first_name as 'First',
    MIDDLE_Name as 'Middle',
    last_name as 'Last'
  )
)

This returns

STUDENT_ID  FIELD_NAME  VALUE
123         First       Joe
123         Middle      F
123         Last        Blow