create table Test

(id int, String varchar(50) )

​

insert into Test values (1, 'Eggs')

insert into Test values (2, 'Bacon')

insert into Test values (3, 'Bacon Smoked')

insert into Test values (4, 'Other Bacon')

​

​

SELECT *

FROM

(

SELECT 

  Eggs = SUM(CASE WHEN String LIKE '%Eggs%' THEN 1 ELSE 0 END),

  Bacon = SUM(CASE WHEN String LIKE '%Bacon%' THEN 1 ELSE 0 END)

FROM Test 

) AS Test

UNPIVOT

(

  Quantity FOR Foods IN (Eggs, Bacon)

) AS Result