Building on the CASE statement version, use of cross apply to simplify

;WITH TestData (RandomString)

AS

(

SELECTA.RandomString

FROM(

VALUES('Chuff'),

('Chuff1'),

('Chufff'),

('Chuffty'),

('ff'),

('uff'),

('huff')

) AS A(RandomString)

)

SELECTCA1.GroupedThing,

Cnt = COUNT(*)

FROMTestData AS TD

CROSS

APPLY(SELECT CASE WHEN TD.RandomString LIKE 'Chu%' THEN 'Group1' ELSE 'Group2' END) AS CA1(GroupedThing)

GROUPBY CA1.GroupedThing