I'm trying to build a pivot table from three tables. I've been reading for example MySQL pivot with 3 tables but seem to get nowhere. Or have I been wasting my time and should have built the output in the programming language from the results of three queries instead of doing it in a single SQL query?
My tables are:
Table 1:
id | code | description etc.
1 | paraA1 | ...
2 | paraA2 | ...
3 | paraB1 | ...
4 | paraB2 | ...
5 | paraB3 | ...
....
n | paraZn | ...
Table 2:
id | tr | set | etc.
1234 | 11 | 86 | ...
1235 | 13 | 86 | ...
1236 | 14 | 86 | ...
1237 | 18 | 86 | ...
1238 | 11 | 87 | ...
1239 | 12 | 87 | ...
1240 | 13 | 87 | ...
Table 3:
id | table1_id | table2_id | value
345 | 1 | 1234 | 20
346 | 3 | 1237 | 17
347 | 2 | 1235 | 42
348 | 5 | 1235 | 33
And the output table I want is built with these restrictions:
- table1.code as columns, more rows could be added in this table so it should be done dynamically
- table2.tr values as rows WHERE set = x (i.e. for one set at the time)
- table3.value values as cell contents
- also blank rows and columns are included (in this example row 14 and column paraB2)
So the output from the above tables for set 86 would be
id | paraA1 | paraA2 | paraB1 | paraB2 | paraB3 | ... paraZn | set
11 | 20 | | | | | | 86
13 | | 42 | | | 33 | | 86
14 | | | | | | | 86
18 | | | 17 | | | | 86
Database structure for source tables unfortunately cannot be modified.
