Update column by removing text in braces

I need to update single column for all the rows in a table. The update should happen in such way that data needs be filtered out by removing all text where braces are appended for the string and swap the strings separated by ','.

Example:

Column A
---------
Ab,cde(123)
Ab,cde
yz,kol
yz,kol(567)
uv,xyz
first name,lastname (123456)

As per the above example, I need the final data to be something like this

Output
---------
cde, Ab
cde, Ab
kol, yz
kol, yz
xyz,uv
lastname,first name

Please let me know how we can achieve the above output in SQL Server.

Answer

Try this SQL Code:

;WITH cte
AS (
    SELECT ColumnA AS [Column1]
        ,CASE 
            WHEN charindex('(', ColumnA) > 0
                AND charindex(')', ColumnA) > charindex('(', ColumnA)
                THEN stuff(ColumnA, charindex('(', ColumnA), charindex(')', ColumnA) - charindex('(', ColumnA) + 1, '')
            ELSE ColumnA
            END AS [Column2]
        ,0 AS [Level] FROM t1

    )
    ,cte1
AS (
    SELECT *
        ,row_number() OVER (
            PARTITION BY [Column1] ORDER BY [Level] DESC
            ) AS Rn
    FROM cte
    )
SELECT Column1, Column2 , 
REPLACE( REPLACE(cte1.Column2 , SUBSTRING(Column2 , 0 ,  CHARINDEX(N',' , Column2)) , N'')  , N',' , N'') + N', ' + SUBSTRING(Column2 , 0 ,  CHARINDEX(N',' , Column2)) AS OutPut
FROM cte1 where Rn = 1