sql left join and duplicates in result

Say I have 2 tables, A and B, each A entity can possibly have multiple B entities, in one case if I want to get all B's of some certain A's, I might do it with a simple left join

select A.id aid,B.id bid from A
left join B on B.aid = A.id
where A.id = 1

and it will return a result set like

aid   bid
1     1
1     2
1     3

As you can see for the first column, all those 1's are kinda duplicates. Is it possible to modify the SQL statement to let him return a result like

aid    bid
 1      1,2,3

in other words to link all the bid's together as one entity?

Also what if there's another table C, and each A can have multiple C's, how to I make the SQL return a result set like

 aid    bid    cid
  1     1,2,3   1,2

instead of

 aid     bid    cid
  1       1      1
  1       2      1
  1       3      1
  1       1      2
  1       2      2
  1       3      2

Thank you very much!

From stackoverflow

• What DBMS are you using?

  I can't speak for others, but in MySQL, starting from 4.1, you can use GROUP_CONCAT

  http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

  EG:

  select A.id aid, GROUP_CONCAT(DISTINCT B.id) bid from A
  left join B on B.aid = A.id
  where A.id = 1
  GROUP BY a.id
  

  Shawn : Would group_concat be any performance issue?

  benlumley : I don't think it has any performance issue that isn't there already in this type of query.

• Try using the COALESCE function.

  http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string