SQL Server 2000

I have two tables(tblParent and tblChild) as follows :
HTML Code:
tblParent          tblChild
PID                 CID     PID       Name
             
1           |         89     1         Fred
2           |         90     1         Jane
                      91     2         Rod
The result I want from my query is as follow:
Code:
PID         Name
1            Fred, Jane
2            Rod

Is this possible and if so can somone show me the sql required?

Thanks In Advance all