Combining multiple rows by stuffing columns into list

Question

I have four tables employees, associations_employees, associations, association_items. The select query below yields me the joined rows.

Note: I have made tags for SQL as well as Coldfusion Language, this is because I am using coldfusion as my scripting language. I am not sure whether I should rely on SQL or use my scripting language.

Query

SELECT  AE.userid, E.firstname,
    A.title, AI.itemvalue
FROM  associations_employees AE
    INNER JOIN employees E on E.userid = AE.useridFK
    INNER JOIN associations A on A.associationid = AE.associationidFK
    INNER JOIN association_items AI on AI.associationidFK = AE.associationidFK

Current select output

userID  firstname  title           itemvalue
------  ---------  -----           ---------
5603    Jesh       Learner Type    Family Literacy   
5603    Jesh       Learner Type    Elementary School 
5603    Jesh       Learner Type    Academic             
5603    Jesh       Personnel Type  Staff             
5605    jennone    Personnel Type  Site Supervisor   
5605    jennone    Personnel Type  Rops member       
5607    Sharon     Personnel Type  Rops member       
5607    Sharon     Personnel Type  Site Supervisor
5607    Sharon     Mentor Type     High School
5607    Sharon     Mentor Type     Op. Read
5607    Sharon     Mentor Type     Enrichment
5607    Sharon     Mentor Type     General

As you can notice, there are multiple rows which are similar apart from 'itemvalue' column. I need to combine these rows to produce the following result.

Needed output

userID  firstname  title           itemvalue
------  ---------  ------          ---------
5603    Jesh       Learner Type    Family Literacy;Elementary School;Academic
5603    Jesh       Personnel Type  Staff
5605    jennone    Personnel Type  Rops member;Site Supervisor;Staff
5607    Sharon     Personnel Type  Rops member;Site Supervisor
5607    Sharon     Mentor Type     Enrichment;General;High School;Op. Read

Answer 1

You can use the STUFF method to achieve this:

SELECT AE.userid,
       E.firstname,
       A.title,
       STUFF((SELECT ',' + [AI.itemvalue]
              FROM association_items AI
              WHERE AI.associationidFK = AE.associationidFK
              FOR XML PATH('')), 1, 1, '') AS itemvalue
FROM associations_employees AE
INNER JOIN employees E ON E.userid = AE.useridFK
INNER JOIN associations A ON A.associationid = AE.associationidFK
GROUP BY AE.userid, E.firstname, A.title,

This has't been tested so may require some fine tuning.