SQL to concatenate

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

Here you go.

Code:

---

SELECT P.PID, C.Name
from tblParent P INNER JOIN tblChild C ON P.PID = C.PID

---

This will give the following output

Code:

---

PID        Name
1            Fred,
1            Jane
2            Rod

---

Please add to my rep if this helps. thanks.

Thats not what I wanted.
I need to, in this case return 2 rows. I want the fields concatenated

try searching for "concatenation" in posts by slzamany ... I know I've seen him post that before... it's one of those tricks, when I see it, I think "how can that work" ... but it does. Unfortunately it isn't something I use, so it never sticks.

-tg

To handle multiple child records without having to create an unnecessarily complex join, create a stored procedure that generates the CSV (stored procedure retrieves records, iterates through them, builds CSV and returns CSV to caller).

You should be mindful of data sizing issues though, e.g. report item widths, since you don't know how long string returned by procedure will be in the future.

You can do it with a function

Code:

---

CREATE FUNCTION fnConcatenateNames(@ID INT)
RETURNS VARCHAR(1000) AS
BEGIN
  DECLARE @names VARCHAR(1000)
  SELECT @names = COALESCE(@names + ', ', '') + COALESCE(Name,'')
  FROM b
  WHERE PID = @ID
  return @names
END

---

And use it like

Code:

---

SELECT ID
    , dbo.fnConcatenateNames(ID) AS Names
FROM a
GROUP BY ID

---