SQL Parsing PV Variable Example

WITH SplitData AS (
    SELECT 
        [RecoveryKey],
		[DateTime],
        [DialedNumberString],
		[Variable9],
        VALUE AS PartValue,
        ROW_NUMBER() OVER (PARTITION BY RecoveryKey ORDER BY (SELECT NULL)) AS PartNumber
    FROM Route_Call_Detail RCD 
    CROSS APPLY STRING_SPLIT(Variable9, '|')
	WHERE Variable9 LIKE 'TST|%' AND DateTime > GetDate()-1
)
SELECT 
    RecoveryKey,
	DateTime,
	DialedNumberString,
	Variable9,
    MAX(CASE WHEN PartNumber = 1 THEN PartValue END) AS Part1,
    MAX(CASE WHEN PartNumber = 2 THEN PartValue END) AS Part2,
    MAX(CASE WHEN PartNumber = 3 THEN PartValue END) AS Part3,
    MAX(CASE WHEN PartNumber = 4 THEN PartValue END) AS Part4,
    MAX(CASE WHEN PartNumber = 5 THEN PartValue END) AS Part5,
    MAX(CASE WHEN PartNumber = 6 THEN PartValue END) AS Part6,
    MAX(CASE WHEN PartNumber = 7 THEN PartValue END) AS Part7,
    MAX(CASE WHEN PartNumber = 8 THEN PartValue END) AS Part8,
    MAX(CASE WHEN PartNumber = 9 THEN PartValue END) AS Part9,
    MAX(CASE WHEN PartNumber = 10 THEN PartValue END) AS Part10,
    MAX(CASE WHEN PartNumber = 11 THEN PartValue END) AS Part11
FROM SplitData
GROUP BY RecoveryKey, DateTime, DialedNumberString, Variable9
ORDER BY RecoveryKey;