Maybe this is impossible?
I have four tables of identical structure but not related.
I am running a Select on four tables with three Union All. I would like the result from the Select and Unions (which work fine) to be routed into a Crosstab-type query to gvie the result from each table in columns. But here is the difficult bit, I would like to do this in one query.
As I am new to MySQL, I am confused by subqueries and temporary tables and how to reference them, though I realise the latter may be the way to go.
Anyway, this is the code and I would appreciate some help or advice.
The above code takes it this far:
T1| 1| 2003| 20.00
T2| 1| 2003| 30.00
T3| 1| 2003| 45.00
T4| 1| 2003| 55.00
I would like to know how the results from the unions can pivot on the customer and year as follows:
CustId| Yearid| T1| T2| T3| T4
1 | 2004 | 20| 30| 40| 50
2 | 2005 | 10| 25| 67| 32
Any ideas?
John
I have four tables of identical structure but not related.
I am running a Select on four tables with three Union All. I would like the result from the Select and Unions (which work fine) to be routed into a Crosstab-type query to gvie the result from each table in columns. But here is the difficult bit, I would like to do this in one query.
As I am new to MySQL, I am confused by subqueries and temporary tables and how to reference them, though I realise the latter may be the way to go.
Anyway, this is the code and I would appreciate some help or advice.
Code:
SELECT "T1" AS Table1,Custid,yearid, SUM(amount) AS "Total"
FROM table1
WHERE yearid>0
GROUP by yearid
UNION ALL
SELECT "T2" AS Table2,Custid,yearid, SUM(amount)
FROM table2
WHERE yearid>0
GROUP by yearid
UNION ALL
SELECT "T3" AS Table3,Custid,yearid, SUM(amount)
FROM table3
WHERE yearid>0
GROUP by yearid
UNION ALL
SELECT "T4" AS Table4,Custid,yearid, SUM(amount)
FROM table4
WHERE yearid>0
GROUP by yearid
The above code takes it this far:
T1| 1| 2003| 20.00
T2| 1| 2003| 30.00
T3| 1| 2003| 45.00
T4| 1| 2003| 55.00
I would like to know how the results from the unions can pivot on the customer and year as follows:
CustId| Yearid| T1| T2| T3| T4
1 | 2004 | 20| 30| 40| 50
2 | 2005 | 10| 25| 67| 32
Any ideas?
John