Query that returns comma separated list and allows to search inside it

I have MainTabl开发者_如何学Goe with 2 fields: MAIN_ID and DESCRIPTION and SeoncdaryTable with 2 fields: MAIN_ID (FK to MainTable) and EXTRA_INFO.

I would like to have a query that returns one record per MainTable record (select * from MainTable) plus having another result that shows me in comma separated fashion all the records of SecondaryTable (of course matcihing the MAIN_ID).

Moreover I would like in the WHERE condition to be able to filter for SecondaryTable fields.

Example:

MainTable:

MAIN_ID, DESCRIPTION

1, One

2, Two

3, Three

4, Four

Secondary Table

MAIN_ID; EXTRA_INFO;

1; ALPHA

1; BETA

1; GAMMA

3; BETA

4; ALPHA

4; GAMMA

and I want to query all the records where secondary table contains GAMMA, the expected results are

1; One; ALPHA, BETA, GAMMA

4, Four; ALPHA, GAMMA

how to achieve that?

declare @MainTable table (MAIN_ID int identity, [DESCRIPTION] varchar(10));
insert into @MainTable values('One'),('Two'),('Three'),('Four'),('Five');

declare @SecondaryTable table (MAIN_ID int, EXTRA_INFO varchar(10));
insert into @SecondaryTable values
(1, 'ALPHA'), (1, 'BETA'), (1, 'GAMMA'), (3, 'BETA'), (4, 'ALPHA'), (4, 'GAMMA');

select MT.MAIN_ID,
       MT.[DESCRIPTION],
       stuff((select ', '+ST.EXTRA_INFO
              from @SecondaryTable as ST
              where MT.MAIN_ID = ST.MAIN_ID
              for xml path(''), type).value('.', 'varchar(max)'), 1, 2, '') as EXTRA_INFO
from @MainTable as MT
where MT.MAIN_ID in (select MAIN_ID      
                     from @SecondaryTable
                     where EXTRA_INFO = 'GAMMA')

ave you tried using STUFF this command stuffs all the result rows into a single column, here you can provide the delimiter of your choice.