There might be other ways. However, I'm not sure what do you need as you're just giving a piecemeal of the information. I can't see a reason why the solution...

Here's a possibility. Although, I didn't test for performance.

WITH CTE AS(
  SELECT *,
   DENSE_RANK() OVER(PARTITION BY a ORDER BY b) dr,
  ...

This is easy using the Pattern Splitter by Chris Morris that can be found on the following article: Splitting Strings Based on Patterns - SQLServerCentral

Here's the...