sql left join and duplicates in result

Question

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!

Answer 1

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

Answer 2

Try using the COALESCE function.

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