sqlviewpivottranspose

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:

FormType FieldName ColumnName
STK COL001 Colour
STK SIZ001 Size1
STK LIC001 License

Table: FormData:

StockCode Colour Size1 License
12345 R 2 XX
ABCDE L 1 AA
AB123 G 3 BB

Required resulting data from view:

FormType StockCode FieldName Value
STK 12345 COL001 R
STK 12345 SIZ001 2
STK 12345 LIC001 XX
STK ABCDE COL001 L
STK ABCDE SIZ001 1
STK ABCDE LIC001 AA
STK AB123 COL001 G
STK AB123 SIZ001 3
STK AB123 LIC001 BB

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.


Solution

  • 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