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 Prod

Solution 1:

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