I'm trying to create a view and think pivot command is what I need, but can't get it to do what I need. Here's what I have for data:

Serial_Num Machine Value0 Value1 Value2
XYZ123 1 1230100 1230101 1230102
XYZ123 2 1230200 1230201 1230202
XYZ123 3 1230300 1230301 1230302
XYZ124 1 1240100 1240101 1240102
XYZ124 2 1240200 1240201 1240202
XYZ124 3 1240300 1240301 1240302

Here is what I want for an output:


Serial_Num Mach1_Value0 Mach1_Value1 Mach1_Value2 Mach2_Value0 Mach2_Value1 Mach2_Value2 Mach3_Value0 Mach3_Value1 Mach3_Value2
XYZ123 1230100 1230101 1230102 1230200 1230201 1230202 1230300 1230301 1230302
XYZ124 1240100 1240101 1240102 1240200 1240201 1240202 1240300 1240301 1240302

I hope this makes sense, I've colour coded the data in an attempt to help explain.

Below is the closest I can get, but this query does not do what I want it to.

SELECT SERIAL_NUM,
[1] AS MC1,
[2] AS MC2,
[3] AS MC3
FROM
(SELECT SERIAL_NUM,
MACHINE,
VALUE00,
VALUE01,
VALUE02
FROM dbo.QA_DATA) AS SourceTable

PIVOT (
AVG(VALUE00)
FOR MACHINE IN (
[1],
[2],
[3]
)
) AS PIVIOTTABLE;

Thanks in advance