Dynamically getting data from a oracle table

Question

Need your help in the below scenario.

Table1
    Columns
        Unique_id   number  
        Name        varchar2(20)
        desc        varchar2(20)
        Column_Name        varchar2(20) /* Contain Column name of Table2 like Col1, Col2, Col3*/
        
Table2
    Columns
        Unique_id   number  FK from Table1
        Col1        varchar2(20)
        Col2        varchar2(20)
        Col3        varchar2(20)

I want to write a query to get the below columns.

Name, desc, Column_name, Value

The Value should be from Table2. All the columns(Col1, Col2, Col3) may contain data or only one column may contain data.

Need help in how to get that, i used Decode to get that. In my actual sceanrio there are around 200 such columns are there, is there any other better (performant) way to get solve the issue.

Thanks, Vijay

Answer 1

Since you mentioned that if there is more than 1 column containing values in Table2, resulted rows would also be more than one. So you can use UNION ALL first to generate multiple rows and then can join that with Table1-

SELECT T1.Name, T1.desc, T1.Column_name, T2.Value
  FROM Table1 T1
  JOIN (SELECT Unique_id, Col1 Value
          FROM Table2
         WHERE col1 IS NOT NULL
         UNION ALL
        SELECT Unique_id, Col2
          FROM Table2
         WHERE col2 IS NOT NULL
         UNION ALL
        SELECT Unique_id, Col3
          FROM Table2
         WHERE col3 IS NOT NULL
        ... ) T2      --  Repeat this step for how much columns are there in your table.  
        ON T1.Unique_id = T2.Unique_id;