SQL Flatten, Pivot, and Query

I am joining quite a bit of data together, but i need to pivot that data and then select the fields I want out of that query. The data looks a bit like this:

ID    OrderNo Product ProductID MetricID Metric        value
111 G456789 M12345 10           89          Machine    7
111 G456789 M12345 10           90          MachineID 2020
111 G456789 M12345 10           91          Length      1951

The resultset above could have any number of Metrics (50 or even 100 or more). The system is configurable such that they can add metrics, which means I can't really 'hard-code' anything that would invalidate the SQL.

When the ID is distinct (as in 111 above), this would be a single row. The OrderNo, Product, and ProductID would also be the same, but ID is the key to the pivot.

This is how the Pivoted result set needs to look after:

ID OrderNo Product ProductID Machine MachineIDLength
111 G456789 M12345 10 7 2020 1951

Since there could be any number of metrics, I would want them all in that row.

NOW - After I have that all said and done... I will want to query out that resultset for ONLY the variables I need. So, it is possible (in the example above) that I would only query out ID, Product, Machine, and Length.

I'm sure this can be done.. just confused how! Been playing around for hours and hours trying to figure it out :(

Try using a case statement inside an aggregation, something like this:

SELECT ID,OrderNo, Product, ProductID,
MAX(CASE WHEN Metric='Machine' THEN value END) Machine,
MAX(CASE WHEN Metric='MachineID' THEN value END) MachineID,
MAX(CASE WHEN Metric='Length' THEN value END) Length
FROM Table1
GROUP BY ID,OrderNo, Product, ProductID