How to create a SQL view to transpose data using two tables

I need to find a way to create a SQL view that allows me to transpose some data that in one table a particular column contains the column names from another table.

Table: FormControl:

Table: FormData:

Required resulting data from view:

The field names/columns might change, so ideally I need the view to be dynamic but, if that's not possible, a fixed one with just the current fields/columns in is fine for now.

Any ideas please?

Have tried using PIVOT but have got a bit lost in how to do it.

A cross join should be able to able to fetch all the combination from FormControl and FormData table and a case statement to select the rows for each column.

Please mention the database type, which will help in visiblity.For now I have tested in Oracle database.

Here is the fiddle which shows the expected output :

SELECT
    fc.formtype,
    fd.stockcode,
    fc.fieldname,
    CASE fc.column_name
        WHEN 'Colour' THEN fd.colour
        WHEN 'Size1' THEN fd.size1
        WHEN 'License' THEN fd.license
        ELSE NULL
    END AS value
FROM
    formcontrol fc
CROSS JOIN formdata fd
WHERE fc.formtype = 'STK'
order by fd.stockcode;

Expected Output

Fetch columns dynamically : Columns name can be fetched dynamically by using a procedure to loop through the columns and generate the query.

DECLARE
  v_sql VARCHAR2(4000);
BEGIN
  v_sql := 'SELECT fc.formtype, fd.stockcode, fc.fieldname, ';
  v_sql := v_sql || ' CASE  fc.column_name ' ;

  FOR r IN (SELECT column_name FROM formcontrol WHERE formtype = 'STK') LOOP
    v_sql := v_sql || ' WHEN ''' || r.column_name || ''' THEN fd.' || r.column_name || ' ' ;
  END LOOP;

  v_sql := v_sql || ' ELSE NULL END AS value ' ;

  v_sql := RTRIM(v_sql, ', ') || ' FROM formcontrol fc CROSS JOIN formdata fd WHERE fc.formtype = ''STK''  ORDER BY fd.stockcode';

  EXECUTE IMMEDIATE v_sql;

DBMS_OUTPUT.PUT_LINE(v_sql);
END;
/

It generates the same query as above as shown in the fiddle