Arithmetic on DB Table's Vertical Data-CodePudding

If I have a table with few columns like the following, then arithmetic is easy, wherein the formula for result is stored elsewhere.

Table Result

id	name	columnA	columnB	columnC	result
1	abc	10	2	4	A*B/C=20

In case the Values of ColumnA, ColumnB, and ColumnC are stored in Master Table and The Data Values in another Table as follows, then what should be the approach

Table Master

id	related_to	column_name
1	test1	columnA
2	test1	columnB
3	test1	columnC
4	test2	columnA
5	test2	columnB

Table Formula

id	related_to	Formula
1	test1	columnA * columnB / columnC
2	test2	(columnA columnB ) / 2

Table Data_Source

id	related_to	column_id	column_name	column_value
1	test1	1	columnA	10
2	test1	2	columnB	2
3	test1	3	columnC	4
4	test2	1	columnA	8
5	test2	2	columnB	8

Please advise on possible SQL statements for populating the Table Result

CodePudding user response：

Something like this. It is limited to one JOIN per column (so not an arbitrary number of columns where a crosstab would be the way to go) and there may be better methods, but this answers your question I think. Basically it reconstitutes your original table. I haven't tested it for typos, etc.

SELECT related_to name, formula.formula result, cola.column_value columnA, colb.column_value columnB, colc.column_value columnC
FROM master 
    JOIN formula ON master.related_to = formula.related_to
    JOIN data_source cola ON 'columnA' = data_source.column_name
            AND master.related_to = data_source.related_to
    JOIN data_source colb ON 'columnB' = data_source.column_name
            AND master.related_to = data_source.related_to
    JOIN data_source colb ON 'columnC' = data_source.column_name
            AND master.related_to = data_source.related_to