Here's another way that doesn't worry much about how many spaces are included.

WITH SampleData (Condition, Columnname) AS

(

SELECT 'akraft,crunckel','TN'

UNION ALL SELECT 'AL,AZ','State'

UNION ALL SELECT 'Atlanta,Austin-San Marcos','MAC'

UNION ALL SELECT 'aney,aventura','Area'

UNION ALL SELECT 'Alexandria, VA,Arlington, TX','Market'

UNION ALL SELECT 'Alpharetta,Alexandria','City'

UNION ALL SELECT '001,002','StoreS'

UNION ALL SELECT 'SameStore,HeitmanI','Store Type'

UNION ALL SELECT 'InLast6Months,CS-CC','Space Types'

),

SplitStrings AS

(

SELECT *

FROM SampleData

CROSS APPLY dbo.PatternSplitCM(Condition, '[, ]')

)

SELECT Condition, ColumnName

,NewCondition=

(

SELECT CASE [Matched] WHEN 0 THEN ''''+Item+'''' ELSE ',' END

FROM SplitStrings b

WHERE a.Condition = b.Condition AND a.ColumnName = b.ColumnName

ORDER BY ItemNumber

FOR XML PATH('')

)

FROM SplitStrings a

GROUP BY Condition, ColumnName;

PatternSplitCM can be found and is explained in the 4th article in my signature links.