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

Question

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.

samhita · Accepted Answer · 2024-11-13 13:58:19Z

1

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

edited Nov 13, 2024 at 13:58

answered Nov 12, 2024 at 22:42

samhita

4,2512 gold badges12 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andrew Longland Over a year ago

Thanks Samhita. This is in MS SQL Server. What you've given is great, but I'm wondering if there's a way to make it dynamic so that if additional columns are added to the table in future, it will handle it?

samhita Over a year ago

Thanks @AndrewLongland, I have updated the response to fetch the columns dynamically.Procedure is used since we need to loop through the columns.Hope it helps!

keithwalsh · Accepted Answer · 2024-11-13 14:21:56Z

The below makes use of MS SQL Server's XML capabilities and will automatically adapt to any new columns added to the FormData table without requiring any modifications.

SELECT
  fc.FormType,
  fd.StockCode,
  fc.FieldName,
  -- Convert xml node value to varchar
    x.col.value('.', 'varchar(100)') AS Value
FROM
  FormData fd
CROSS APPLY
  -- Convert row to xml format
  (SELECT fd.* FOR XML PATH('row'), TYPE) AS xmlData(xmlCol)
CROSS APPLY
  -- Split xml into column nodes
  xmlData.xmlCol.nodes('/row/*') AS x(col)
INNER JOIN
  -- Match column names with formcontrol
  FormControl fc ON fc.ColumnName = x.col.value('local-name(.)', 'varchar(100)')
WHERE
  x.col.value('local-name(.)', 'varchar(100)') <> 'StockCode';

Output:

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

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related