Okay, I resolved this in such a simple fashion that I'm embarrassed that I didn't think of it sooner. Each record has a type number, i.e. 1 for header, 2 for detail etc.
SELECT RRE,
ClaimantID,
RowType,
TextData
FROM ( SELECT
RRE,
ClaimantID,
RowType,
CASE (RowType)
WHEN 1 THEN SUBSTRING(TextData,1,132)
WHEN 2 THEN SUBSTRING(TextData,1,180)
WHEN 3 THEN SUBSTRING(TextData,1,18)
WHEN 4 THEN SUBSTRING(TextData,1,32)
END AS TextData
FROM dbo.TMP_ClaimValidationFinalTextData
) AS Y
ORDER BY ClaimantID, RowType
;
Nice and simple.
Just wondering ... did you choose SUBSTRING() rather than LEFT() for any particular reason?
I was thinking about strings and SUBSTRING was the first to come to mind. Is LEFT more efficient?
"Beliefs" get in the way of learning.