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.
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;
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