In SQL 2000, it's easy enough to return a delimited string
as part of a resultset. Say, for instance, you want to
display employee names along with a comma-delimited string
of all the phone numbers each employee has. Typically you
would first create a UDF as follows:

CREATE FUNCTION EmpPhones (@EmpID INT)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @phones VARCHAR(1000)
SELECT @phones = ISNULL(@phones + ',', '') +
PhoneNo
FROM EmpPhones
WHERE EmpID = @EmpID
RETURN (@phones)
END

And then you would call this UDF from within a SELECT
statement, as follows:

SELECT FirstName, LastName,
dbo.EmpPhones(EmpID) AS [Phone Numbers]
FROM Employees

Thus you would get back a result set something like this:

FirstName LastName Phone Numbers
-------------------------------------------------
John Smith 555-1212,123-4567,987-6543
Bill Jones 444-5555,111-2222,333-4444
Jane Brown 321-4567,456-7890

My question is: how would you do this operation without
using a UDF - e.g., you wanted your code to be compatible
with SQL 7.0? I realize, of course, that you could do
this with a cursor or a WHILE loop - but I'm looking for a
set-oriented solution, if possible.

Doing it in the client application is the best option. Here is another way
to do this in T-SQL:

SELECT FirstName, LastName,
MAX(CASE Nbr WHEN 1 THEN PhoneNo ELSE SPACE(0) END) +
MAX(CASE Nbr WHEN 2 THEN ',' + PhoneNo ELSE SPACE(0) END) +
...
MAX(CASE Nbr WHEN n THEN ',' + PhoneNo ELSE SPACE(0) END)
FROM (
SELECT EmpId, FirstName, LastName, PhoneNo
(SELECT COUNT(*)
FROM EmpPhones e1
WHERE e1.EmpId = e2.EmpId
AND e1.PhoneNo <= e2.PhoneNo )
FROM EmpPhones e2
) D ( EmpId, FirstName, LastName, PhoneNo, Nbr)
GROUP BY FirstName, LastName ;

--- Anith

Read a book on RDBMS and underline the parts about First Normal Form
(1NF). Then read a book on Client/server architectures and the job of
the front end (display), the job of the of the back end (data retrieval)
and why the separation is so important. Then stop trying to use SQL as
a report writer/app develeopment tool.

Oh, if you just don't care about doing the job right, someone will give
you one of several standard highly proprietary kludges for this.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***