Combining multiple rows in SQL Server -

i can't figure out myself. hope me.

i have 3 tables: users, cards, referees , users_cards

users:

id  |   username  |   referee_id ---------------------------- 1   |   u1        |    1 2   |   u2        |    1 3   |   u3        |    2 

referees:

id  |   refname -------------- 1   |   ref1 2   |   ref2 

cards:

id  |   cardname -------------- 1   |   card1 2   |   card2 

users_cards:

user_id  |   card_id  |   color   |   number ------------------------------------------- 1        |   1        |   red     |   123 1        |   2        |   yellow  |   312 2        |   2        |   yellow  |   523 3        |   1        |   red     |   344 

the result want is:

id   |   username   |   refname   |   cards 1    |      u1      |    ref1     |   card1: red (123), card2: yellow (312) 2    |      u2      |    ref1     |   card2: yellow (523) 3    |      u3      |    ref2     |   card1: red (344) 

and on...

all can multiple rows when user has more 1 card. how can combine them this.

thank anyone, me!´

edit:

currently i'm using left joins

select username, refname cardname, color, number, users left join referees on users.referee_id = referees.id left join users_cards on users.id = users_cards.user_id left join cards on dbo.users_cards.card_id = cards.id 

select users.id, username, refname,  cards = (     select         stuff((             select ', card' + cast(row_number() on (order cards.id) varchar)                    +': '+ color + '(' + cast(number varchar) +')'                           users_cards               left join cards on dbo.users_cards.card_id = cards.id            users.id = users_cards.user_id            xml path('')), 1, 1, '')    ) users left join referees on users.refereeid = referees.id