SQL Keywords

STRING_AGG

SQL Query:

select STRING_AGG(erc.Code, ';') as ProductLine From EcoResCategory erc

join EcoResProductCategory erpc

on erc.RecId = erpc.Category 

join INVENTTABLE it

on erpc.Product  = it.PRODUCT

where  it.ITEMID  = 'TST702'  and erc.Code  != '' and it.DATAAREAID = 'USMF' 

Output: 

2;7;13;G;9;3;Z;11;17;D;B;F;8;6;1;05

Execute:

Execute ('

Declare @val Varchar(MAX)

Select @val = COALESCE(@val + '','' + erc.Code, erc.Code) 

        From EcoResCategory erc

join EcoResProductCategory erpc

on erc.RecId = erpc.Category 

join INVENTTABLE it

on erpc.Product  = it.PRODUCT

where  it.ITEMID  = 'TST702'  and erc.Code  != '' and it.DATAAREAID = 'USMF' 

Group by erc.Code order by erc.Code asc

Select @val')

COALESCE

Declare @val Varchar(MAX)

Select @val = COALESCE(@val + ';' + erc.Code, erc.Code) 

        From EcoResCategory erc

join EcoResProductCategory erpc

on erc.RecId = erpc.Category 

join INVENTTABLE it

on erpc.Product  = it.PRODUCT

where  it.ITEMID  = 'TST702'  and erc.Code  != '' and it.DATAAREAID = 'USMF' 

Group by erc.Code order by erc.Code asc

Select @val