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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.