Breaking columns into rows by column name-CodePudding

I have the following Table...

TABLE_A

emp_id | A_column_1 | A_column_2
 80001 | Apple      | 10
 80002 | Orange     | 3
 80003 | Banana     | 5

I'm able to capture the columns of this table by querying all_tab_columns

 SELECT column_name
 FROM all_tab_columns
WHERE table_name = 'TABLE_A';

result...

| column_name |
| A_column_1  |  
| A_column_2  |

I'd like write a query that breaks out the columns in TABLE_A into rows that match their corresponding column name in the all_tab_columns query. The result would looks like...

emp_id | View_column_1 | View_column_2
 80001 | Apple         | A_column_1
 80001 | 10            | A_column_2
 80002 | Orange        | A_column_1
 80002 | 3             | A_column_2
 80003 | Banana        | A_column_1
 80003 | 5             | A_column_2

Is this possible without hard coding values?

CodePudding user response：

Select emp_id, A_column_1 as View_column_1, 'A_column_1' as View_column_2 From TABLE_A Union all Select emp_id, A_column_2 as View_column_1, 'A_column_2' as View_column_2 From TABLE_A .

You can use order by if necessary:)

CodePudding user response：

According to Alex Poole - How to Unpivot Dynamic Columns in Oracle, you may try the solution below :

Simply put,

First LISTAGG is to get the dynamic columns
Second LISTAGG is to convert "a_column_1" and "a_column_2" into the same data type
sql_stmt creates a view, so you can SELECT * FROM V_TABLE_A to see the results.

See db<>fiddle

DECLARE
  sql_stmt CLOB;
  subquery CLOB;
  pivot_clause CLOB;

BEGIN
  SELECT 
    LISTAGG(column_name || ' AS ''' || column_name || '''', ',') 
      WITHIN GROUP (ORDER BY column_name),
    LISTAGG(CASE WHEN data_type = 'NUMBER' THEN 'TO_CHAR(' || column_name || ')' ELSE column_name END || ' AS ' || column_name, ',')
      WITHIN GROUP (ORDER BY column_name)
  INTO pivot_clause, subquery
  FROM all_tab_columns
  WHERE table_name = 'TABLE_A'
  AND column_name NOT IN ('EMP_ID');

  sql_stmt := 'CREATE OR REPLACE VIEW V_TABLE_A AS
    SELECT emp_id, view_column_1, view_column_2
    FROM (SELECT emp_id, ' || subquery || ' FROM TABLE_A)
    UNPIVOT (view_column_1 FOR view_column_2 IN (' || pivot_clause || '))';

  DBMS_OUTPUT.PUT_LINE(sql_stmt);
  EXECUTE IMMEDIATE sql_stmt;
END;
/