query result as comma-separated list

[insomniux]

Hi,
I'n in an environment where I cannot make stored procedures. Now I need
to make a query with a subquery in the SELECT part which gives a comma
separated list of results:

SELECT
p.id,
listFunction(SE LECT name FROM names WHERE name_parent=p.i d) AS
'nameList'
FROM projects AS p

This query should return something like:
1, "john,mike,petr a"
2, "bob,carl,sandr a,peter,
etc

listFunction is (of course) not (yet) defined. Is this possible without
the use of stored procedures?
Mike

[Erland Sommarskog]

Mike (mi************ *@hccnet.nl) writes:

Unfortunately my client application does not allow me to use this kind
of syntax. Only SQL.

And your client code is written in? Or why do suggest that you can do
this in client code?

You can do it in SQL, but it is just that it kludgy. And you will have
to accept a upper limit on how long the output may be.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Kevin Haugen (kh*****@pacbel l.net) writes:

This is a function I use to do what you describe. I found the function
someplace on the Internet (don't remember where). Maybe you can modify it
for your use.

DECLARE @NbrList VarChar(300)
Declare @InvoiceID Varchar(30)

SELECT @NbrList = COALESCE(@NbrLi st + ', ', '') +
CAST(C.TRACKING _NO AS varchar(30))

1) Mike wants his list for several rows in this table, so he would
still have to iterate over each id he wants to aggregate for.

2) The above relies on undocumented and undefined behaviour, why I
recommend against using it. The only safe way is build the list
iteratively. For more information look at
http://support.microsoft.com/default.aspx?scid=287515.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[insomniux]

> > Hi,

I'n in an environment where I cannot make stored procedures. Now I need to make a query with a subquery in the SELECT part which gives a comma separated list of results:

SELECT
p.id,
listFunction(SE LECT name FROM names WHERE name_parent=p.i d) AS
'nameList'
FROM projects AS p

This query should return something like:
1, "john,mike,petr a"
2, "bob,carl,sandr a,peter,
etc

listFunction is (of course) not (yet) defined. Is this possible without the use of stored procedures?
Mike

This is a function I use to do what you describe. I found the

function someplace on the Internet (don't remember where). Maybe you can modify it for your use.

DECLARE @NbrList VarChar(300)
Declare @InvoiceID Varchar(30)

SELECT @NbrList = COALESCE(@NbrLi st + ', ', '') +
CAST(C.TRACKING _NO AS varchar(30))

FROM dbo.SHIPPER S INNER JOIN
dbo.SHIPPER_LIN K SL ON S.PACKLIST_ID = SL.PACKLIST_ID
INNER JOIN
dbo.CARTON_LINE CL ON SL.CARTON_ID = CL.CARTON_ID AND
SL.CARTON_LINE_ NO = CL.LINE_NO INNER JOIN
dbo.CARTON C ON CL.CARTON_ID = C.ID
WHERE S.INVOICE_ID = @InvoiceID
SELECT @NbrList
GO

Looks interesting (although behaviour may be unexpected), it may be
useful for me.
I'll let you know
Mike