SQL to concatenate

[venerable bede]

SQL Server 2000

I have two tables(tblParent and tblChild) as follows :

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:

PID         Name
1            Fred, Jane
2            Rod

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

Thanks In Advance all

[gb1983]

Here you go.

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

This will give the following output

PID         Name
1            Fred, 
1            Jane
2            Rod

Please add to my rep if this helps. thanks.

[venerable bede]

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

[techgnome]

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

[leinad31]

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.

[dee-u]

You can do it with a function

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

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