Alright, revised it!
SELECT
ID,
PARSENAME(REPLACE([Subject],';','.'),1) AS Split1,
PARSENAME(REPLACE([Subject],';','.'),2) AS Split2,
PARSENAME(REPLACE([Subject],';','.'),3) AS Split3
INTO #SplitTable
FROM YourTable
SELECT ID,
CASE WHEN Split2 IS NULL AND Split3 IS NULL THEN Split1
WHEN Split3 IS NULL THEN Split2
ELSE Split3 END AS Subject1,
CASE WHEN Split2 IS NULL AND Split3 IS NULL THEN NULL
WHEN Split3 IS NULL THEN Split1
ELSE Split2 END AS Subject2,
CASE WHEN Split3 IS NULL THEN NULL
ELSE Split1 END AS Subject3 FROM #temp2
DROP TABLE #SplitTable
Ugh. This probably wasn't the best way to do this, and it feels kinda inelegant. On a million-row test harness, it works out acceptably in execution speed, but the splitter table would most certainly be more elegant and a lot more efficient. This reminded me of a problem I had to solve myself at some point, but the finer details were a bit more convoluted than I'd hoped.
- 😀