How can we do this in one CTE in SQL

Question

I have data in a column as (LastName, FirstName (EmpId)) I need LastName and FirstName in separate columns. I have created 2 CTEs. I am not being able to achieve the same with one CTE. Also, if there's a better way to achieve this, please advise

IF OBJECT_ID('tempdb..#tempMyTable') IS NOT NULL
    DROP TABLE #tempMyTable

CREATE TABLE #tempMyTable(
    REPORT_TO varchar(25) not null
)
INSERT INTO #tempMyTable
VALUES
('MILLER, BEN (BMR09876)'),
('LON, JOHN (JLON45098)'),
('WILSON, MARY (MWN2345)');
    
WITH CTE_Name AS (
    SELECT [REPORT_TO], LEFT([REPORT_TO], Charindex(',', [REPORT_TO]) -1) as LNAME,
    RIGHT([REPORT_TO], LEN([REPORT_TO]) - CHARINDEX(',', [REPORT_TO])) as FNAME
    FROM #tempMyTable
), CTE_FNAME AS (
    SELECT Report_To, LName, LEFT(FName, CHARINDEX('(', FName)-2) as FName
    FROM CTE_Name
)
SELECT *
FROM CTE_FNAME

Answer 1

You can replace the temp table with a SELECT ... VALUES in a CTE, eg:

with cte_t
as
(

  select * from (values ('MILLER, BEN (BMR09876)'),
                        ('LON, JOHN (JLON45098)'), 
                        ('WILSON, MARY (MWN2345)') ) as X(REPORT_TO)
), CTE_Name as 
(
  SELECT  [REPORT_TO], LEFT([REPORT_TO], Charindex(',', [REPORT_TO]) -1) as LNAME,
  RIGHT([REPORT_TO], LEN([REPORT_TO]) - CHARINDEX(',', [REPORT_TO])) as FNAME
  FROM cte_t
), CTE_FNAME as
(
  SELECT Report_To, LName, LEFT(FName,  CHARINDEX('(', FName)-2) as FName
  FROM CTE_Name
)
select * from CTE_FNAME

Answer 2

You can use a series of CROSS APPLY to calculate things in stages, without using a CTE.

SELECT
  t.Report_To,
  v2.LName,
  LEFT(FName, NULLIF(CHARINDEX('(', FName), 0) - 2) as FName
FROM #tempMyTable t
CROSS APPLY (
    SELECT
      NULLIF(CHARINDEX(',', t.REPORT_TO), 0)
) v1
CROSS APPLY (
    SELECT
      LEFT(t.REPORT_TO, v1.comma -1) as LNAME,
      RIGHT(t.REPORT_TO, LEN(t.REPORT_TO) - v1.comma) as FNAME
) v2;

Note the use of NULLIF to prevent errors in case CHARINDEX can't find the value.