SQL Server 2012: Return multiple strings within a single column for a common row ID

Question

I'm using SQL Server 2012 Express (T-SQL) and I have this table (AwardsPlayers) with the following data in it. My database does not support MySQL. I've looked here and here but I still can't get it right.

If I create a function, will that work in a 4-table join where I have multiple aggregate columns as only one column of many within that join?

playerID            awardID         yearID  lgID notes
puckett         Gold Glove          1986    AL  OF
puckett         Silver Slugger      1986    AL  OF
puckett         TSN All-Star        1986    AL  OF
puckett         Gold Glove          1987    AL  OF
puckett         Silver Slugger      1987    AL  OF
puckett         TSN All-Star        1987    AL  OF
puckett         Gold Glove          1988    AL  OF

I have a larger join I'm using, but from this table I want to be able to return a Query for each playerID and yearID the awardID that correspond to that year.

For instance from the table above the query would return this:

playerID     yearID                  Awards
puckett       1986     Gold Glove, Silver Slugger, TSN All-Star
puckett       1987     Gold Glove, Silver Slugger, TSN All-Star
puckett       1988     Gold Glove

And a blank in the Awards column, should there be no awards for that year.

The Coalesce doesn't work as is, as it wasn't to return an INT, but can't on a VARCHAR type.

My current Query:

--Player Stat Cards, along with Awards, Salary
select
m.namefirst,
m.namelast,
b.yearID,
b.yearID-m.birthyear as Age,
b.G,b.AB,b.R,b.H,b.[2B],b.[3B],b.HR,b.RBI,b.SB,b.BB,b.SO,left(round((b.h*1.000/b.ab),3),5) as Average
,b.IBB,b.HBP,b.SH,b.SF,b.SF,b.GIDP
, **COALESCE(a.awardID + ',' + '',0) +a.awardID**
from batting b
inner join
[master] m on b.playerID=m.playerID
inner join
AwardsPlayers a
on m.playerID=a.playerID
inner join Salaries s
on m.playerID=s.playerID
where m.playerID = 'puckett'
group by
m.namefirst,
m.namelast,
b.yearID,
b.yearID-m.birthyear,
b.G,b.AB,b.R,b.H,b.[2B],b.[3B],b.HR,b.RBI,b.SB,b.BB,b.SO,left(round((b.h*1.000/b.ab),3),5)
,b.IBB,b.HBP,b.SH,b.SF,b.SF,b.GIDP, **COALESCE(a.awardID + ',' + '',0) +a.awardID**

Thanks in advance.

Per the addition of Sswater Shi's contribution...my expression now gives this (much better), however, it returns ALL values found in that column whether or not they are particular to that player for that yearID or not.

Answer 1

Please try this one:

SELECT     playerID, yearID, awards = STUFF(
        (select ',' + [awardID] from AwardsPlayers t
         where playerID = AwardsPlayers.playerID
                   and yearID = AwardsPlayers.yearID
                        for xml path('')) , 1 , 1 , '')
FROM         AwardsPlayers
GROUP BY playerID, yearID