Using the pivot to change from rows to coloumn

[Matthew Camilleri]

Hi all,

I have two queries joined by using the "union" command and then compiled in a view. This gives the output as follows:

------------------------------------------------------------------------------------

CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`%`
SQL SECURITY DEFINER

VIEW `san_stats`.`san_avg_max` AS
SELECT
'SAN01_STATS' AS `SAN01_STATS`,
round(avg(`san_stats`.`ussan01raw`.`cpu`), 0) AS `Avg_CPU`,
round(max(`san_stats`.`ussan01raw`.`cpu`), 0) AS `Max_CPU`
FROM
`san_stats`.`ussan01raw`
WHERE
(`san_stats`.`ussan01raw`.`timeinserted` = (select
max(`san_stats`.`ussan01raw`.`timeinserted`)
FROM
`san_stats`.`ussan01raw`))
union

SELECT
'SAN02_STATS' AS `SAN02_STATS`,
round(avg(`san_stats`.`ussan02raw`.`cpu`), 0) AS `Avg_CPU`,
round(max(`san_stats`.`ussan02raw`.`cpu`), 0) AS `Max_CPU`,

FROM
`san_stats`.`ussan02raw`
WHERE
(`san_stats`.`ussan02raw`.`timeinserted` = (select
max(`san_stats`.`ussan02raw`.`timeinserted`)
FROM
`san_stats`.`ussan02raw`))


----------------------------------------------------------------------------------

This gives the following output:

SAN01_STATS | Avg_CPU Max_CPU
----------------------------
SAN01_STATS | 2 3
SAN01_STATS | 2 4

----------------------------------------------------------------------------------

My aim is to put such an output as follows:

TITLE | SAN01_STATS | SAN02_STATS
------------------------------------
Avg_CPU | 2 | 2
Max_CPU | 3 | 4

----------------------------------------------------------------------------------

This is probably done by using pivot, but unfortunately i don't know were to start. Can you kindly help?

Rgds,

Matthew