469,916 Members | 2,115 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,916 developers. It's quick & easy.

query result as comma-separated list

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(SELECT name FROM names WHERE name_parent=p.id) AS
'nameList'
FROM projects AS p

This query should return something like:
1, "john,mike,petra"
2, "bob,carl,sandra,peter,
etc

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

Jul 23 '05 #1
12 28755
insomniux (mi*************@hccnet.nl) writes:
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(SELECT name FROM names WHERE name_parent=p.id) AS
'nameList'
FROM projects AS p

This query should return something like:
1, "john,mike,petra"
2, "bob,carl,sandra,peter,
etc

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


In SQL 2000, it is actually not possible even with stored procedures
yo write such a function. You will have to write an iterative
solution that iterates over the table, and which uses a temp table
to aggregate the columns.

In SQL2005 you can do this in a single query, thanks to some of the
new XML stuff in SQL 2005.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
I know there are various aggregate functions for numeric columns (like
min, max, ...). Wouldn't it be possible to write an aggregate function
for string-type columns which would enable to write the query as:

SELECT
p.id,
(SELECT listFunction(name) FROM names WHERE name_parent=p.id GROUP
BY name_parent) AS
'nameList'
FROM projects AS p

Erland Sommarskog wrote:
insomniux (mi*************@hccnet.nl) writes:
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(SELECT name FROM names WHERE name_parent=p.id) AS
'nameList'
FROM projects AS p

This query should return something like:
1, "john,mike,petra"
2, "bob,carl,sandra,peter,
etc

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


In SQL 2000, it is actually not possible even with stored procedures
yo write such a function. You will have to write an iterative
solution that iterates over the table, and which uses a temp table
to aggregate the columns.

In SQL2005 you can do this in a single query, thanks to some of the
new XML stuff in SQL 2005.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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


Jul 23 '05 #3
Hi

If this is for a client to display the information, then the best place to
do this would be on the client itself.

John

"insomniux" <mi*************@hccnet.nl> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
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(SELECT name FROM names WHERE name_parent=p.id) AS
'nameList'
FROM projects AS p

This query should return something like:
1, "john,mike,petra"
2, "bob,carl,sandra,peter,
etc

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

Jul 23 '05 #4
I have access to a webinterface which I can feed with SQL, but some
statements are blocked.
I would prefer to create the statement in pure SQL but I think this is
not possible. I know how to get the first and the last value (using top
1 and sorting), but most subqueries return more than 3 rows. I know it
is possible to put all these values in separate columns, but the SQL
statements will become somewhat terrible.

Jul 23 '05 #5
insomniux wrote:
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(SELECT name FROM names WHERE name_parent=p.id) AS
'nameList'
FROM projects AS p

This query should return something like:
1, "john,mike,petra"
2, "bob,carl,sandra,peter,
etc

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


This *should* be very easy to do in your client, looking up the appropriate
functions may be much faster than trying to force it into SQL. The precise
steps you want to be able to execute are (in pseudocode):

// step 1, execute the query in whatever syntax your
// client language uses
//
$some_query = query_command("select ....")

// step 2, pluck out the values of one column to an array,
// your client should have some function for this
//
$some_array = extract_column($some_query,"ColumnA");

// step 3, in PHP the function is "implode", you want a function
// that collapses an array into a delimited string
//
$some_list = implode(",",$some_array);

Best of luck, HTH.

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)
Jul 23 '05 #6
insomniux (mi*************@hccnet.nl) writes:
I know there are various aggregate functions for numeric columns (like
min, max, ...). Wouldn't it be possible to write an aggregate function
for string-type columns which would enable to write the query as:


No, you cannot write your own aggregate functions in SQL 2000.

In SQL 2005 you can - using a CLR language. However, as I understand it,
it does not work very well for this case, at least not if you want the
lists to be ordered.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #7
Kenneth Downs wrote in message
insomniux wrote:
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(SELECT name FROM names WHERE name_parent=p.id) AS
'nameList'
FROM projects AS p

This query should return something like:
1, "john,mike,petra"
2, "bob,carl,sandra,peter,
etc

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


This *should* be very easy to do in your client, looking up the appropriate
functions may be much faster than trying to force it into SQL. The precise
steps you want to be able to execute are (in pseudocode):

// step 1, execute the query in whatever syntax your
// client language uses
//
$some_query = query_command("select ....")

// step 2, pluck out the values of one column to an array,
// your client should have some function for this
//
$some_array = extract_column($some_query,"ColumnA");

// step 3, in PHP the function is "implode", you want a function
// that collapses an array into a delimited string
//
$some_list = implode(",",$some_array);

Best of luck, HTH.


Unfortunately my client application does not allow me to use this kind
of syntax. Only SQL.
Thanks
Jul 23 '05 #8
-P-
"insomniux" <mi*************@hccnet.nl> wrote in message news:11**********************@g14g2000cwa.googlegr oups.com...
I know there are various aggregate functions for numeric columns (like
min, max, ...). Wouldn't it be possible to write an aggregate function
for string-type columns which would enable to write the query as:

SELECT
p.id,
(SELECT listFunction(name) FROM names WHERE name_parent=p.id GROUP
BY name_parent) AS
'nameList'
FROM projects AS p

Yes, it would. The engineers at iAnywhere (SQL Anywhere) figured this one out nearly a decade ago...

SELECT list( [distinct] expression )
FROM tablename

is their aggregate on strings. You can even order the list (in addition to ordering the entire result set), and specify
custom delimiter characters (if you don't like commas).

www.ianywhere.com

-Paul Horan-
Sr. Architect
VCI Springfield, Mass
Jul 23 '05 #9

"insomniux" <mi*************@hccnet.nl> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
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(SELECT name FROM names WHERE name_parent=p.id) AS
'nameList'
FROM projects AS p

This query should return something like:
1, "john,mike,petra"
2, "bob,carl,sandra,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(@NbrList + ', ', '') +
CAST(C.TRACKING_NO AS varchar(30))

FROM dbo.SHIPPER S INNER JOIN
dbo.SHIPPER_LINK 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

Kevin
Jul 23 '05 #10
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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #11
Kevin Haugen (kh*****@pacbell.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(@NbrList + ', ', '') +
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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #12
> > 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(SELECT name FROM names WHERE name_parent=p.id) AS
'nameList'
FROM projects AS p

This query should return something like:
1, "john,mike,petra"
2, "bob,carl,sandra,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(@NbrList + ', ', '') +
CAST(C.TRACKING_NO AS varchar(30))

FROM dbo.SHIPPER S INNER JOIN
dbo.SHIPPER_LINK 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

Jul 23 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Surajit Laha | last post: by
2 posts views Thread by Martin Sarsale | last post: by
ddtpmyra
1 post views Thread by ddtpmyra | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.