One thing I would do is move the conditional expressions from the WHERE clause to each live with their appropriate JOIN (and use better alias names). It won't do much if anything for performance, but it will make the query easier to read and maintain over time. Here's a shortened example:

SELECT m.dataValue AS hp, tHP.dataValue AS tempHp, keithly.dataValue AS keithley
FROM Measurement2 m
LEFT JOIN Measurement2 tHP ON tHP.testPointKey = m.testPointKey
    AND CAST(tHP.dataType as VARCHAR) = 'tempHp'
LEFT JOIN Measurement2 keithly ON keithley.testPointKey = m.testPointKey
    AND CAST(keithly.dataType as VARCHAR) = 'keithley'
WHERE CAST(m.dataType as VARCHAR) = 'hp'

Note the use of LEFT JOIN instead of INNER , as the latter would omit the entire row (all values) if a single item were accidentally missed. This only works when we also move the conditional expressions to live the JOIN.

Separate from that, I agree with the comments about the data type of, well, dataType . The need to use a CAST() here is profoundly awful for performance, as it not only needs to run this same cast repeatedly for all values in the table, but it also breaks any possibility of using an index on the column.

The other thing you can do is use a GROUP BY expression to roll these rows up per testPointKey . Then put a CASE expression in an aggregate function like SUM() for each desired item. This is called conditional aggregation. Here's a shortened example:

SELECT 
    SUM(CASE WHEN CAST(m.dataType as VARCHAR) = 'hp' THEN m.dataValue END) As Hp
   ,SUM(CASE WHEN CAST(m.dataType as VARCHAR) = 'tempHp' THEN m.dataValue END) As tempHp
   ,SUM(CASE WHEN CAST(m.dataType as VARCHAR) = 'keithly' THEN m.dataValue END) As keithly
   -- ...
FROM Measurement2 m
GROUP BY m.testPointKey

Again, fixingdataType to avoid the cast (because what you have now really is broken) would make a profound improvement in the performance.

Finally, this resembles what is known as an Entity-Attribute-Value (EAV) schema pattern. This pattern has its place, especially in the area of plugins and extensibility, but overall it's considered an anti-pattern: that is, something to avoid until you absolutely need it. You will do MUCH better if you can design the table so it more resembles the desired output in the first place.

Due to the helpful comments/answers I found a good alternative in a from of conditional aggregation. With some minor tweaks made (and some more needed such as changing the TEXT to VARCHAR to avoid casting), here is updated code:

WITH
    TestPoints AS
    (
        SELECT * FROM MyTestPoints WHERE sessionKey = '9' --custom number
    ),

    UnfilteredMeasurements AS
    (
        SELECT * FROM MyMeasurements WHERE pointKey IN (SELECT recordKey FROM TestPoints)
    ),

    FilteredMeasurements AS
    (
        SELECT
            pointKey,
            MAX(index) as index,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'hp'              THEN myValue ELSE NULL END) AS hp,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'tempHp'          THEN myValue ELSE NULL END) AS tempHp,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'va'              THEN myValue ELSE NULL END) AS va,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'vc'              THEN myValue ELSE NULL END) AS vc,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'vavc'            THEN myValue ELSE NULL END) AS vavc,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'ia'              THEN myValue ELSE NULL END) AS ia,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'ic'              THEN myValue ELSE NULL END) AS ic,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'phaseVc'         THEN myValue ELSE NULL END) AS phaseVc,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'phaseIa'         THEN myValue ELSE NULL END) AS phaseIa,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'phaseIc'         THEN myValue ELSE NULL END) AS phaseIc,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'tempVa'          THEN myValue ELSE NULL END) AS tempVa,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'tempVb'          THEN myValue ELSE NULL END) AS tempVb,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'tempVc'          THEN myValue ELSE NULL END) AS tempVc,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'tempIa'          THEN myValue ELSE NULL END) AS tempIa,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'tempIb'          THEN myValue ELSE NULL END) AS tempIb,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'tempIc'          THEN myValue ELSE NULL END) AS tempIc,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'tempCta'         THEN myValue ELSE NULL END) AS tempCta,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'tempCtb'         THEN myValue ELSE NULL END) AS tempCtb,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'tempCtc'         THEN myValue ELSE NULL END) AS tempCtc,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'tempSom'         THEN myValue ELSE NULL END) AS tempSom,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'myDate'          THEN myValue ELSE NULL END) AS myDate,
            MAX(CASE WHEN CAST(myType AS VARCHAR) = 'myTime'          THEN myValue ELSE NULL END) AS myTime
        FROM UnfilteredMeasurements
        GROUP BY pointKey
    ),