473,230 Members | 1,526 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,230 software developers and data experts.

How do I create an in-line comma seperated list

Scenario:
Table 1 (a id, b name)
Table 2 (a FKid, d value)

A standard join on a gives me something like:

a1 b1 d1
a1 b1 d2

What I want is:

a1 b1 d1,d2

I can easily do this with a function or cursor, but is is somewhat
slow, and I need to do this a lot and I don't really want to have to
maintain tons of functions or cursors.

Thoughts?
Jul 20 '05 #1
3 5459
Brad Joss (br******@hotmail.com) writes:
Scenario:
Table 1 (a id, b name)
Table 2 (a FKid, d value)

A standard join on a gives me something like:

a1 b1 d1
a1 b1 d2

What I want is:

a1 b1 d1,d2

I can easily do this with a function or cursor, but is is somewhat
slow, and I need to do this a lot and I don't really want to have to
maintain tons of functions or cursors.


This is one of the few cases where a cursor is the best solution.
There are tricks to do this with set-based statements, but they
rely on undocumented and undefined behaviour, and are best avoided.

There is yet another solution: do this client-side if possible.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
br******@hotmail.com (Brad Joss) wrote in message news:<ba**************************@posting.google. com>...
Scenario:
Table 1 (a id, b name)
Table 2 (a FKid, d value)

A standard join on a gives me something like:

a1 b1 d1
a1 b1 d2

What I want is:

a1 b1 d1,d2

I can easily do this with a function or cursor, but is is somewhat
slow, and I need to do this a lot and I don't really want to have to
maintain tons of functions or cursors.

Thoughts?


This works but it's limited to the 8000 max varchar size and it's
T-SQL, not pure SQL:

DECLARE @var varchar(8000)

SET @var = ''

SELECT @var = @var + Name + ','
FROM Persons
GROUP BY Name
ORDER BY Name

SELECT Substring( @Var, 1, Len( @Var ) - 1 ) as List
Jul 20 '05 #3
Diego Buendia (db*******@yahoo.es) writes:
This works but it's limited to the 8000 max varchar size and it's
T-SQL, not pure SQL:

DECLARE @var varchar(8000)

SET @var = ''

SELECT @var = @var + Name + ','
FROM Persons
GROUP BY Name
ORDER BY Name

SELECT Substring( @Var, 1, Len( @Var ) - 1 ) as List


Note that this relies on undefined behaviour, and it may or may not
work depending on your statement, indexes etc.

See http://support.microsoft.com/default.aspx?scid=287515. Pay particular
attention to the first section after the subtitle CAUSE.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Stephane Charette | last post by:
QUICK VERSION: How do I create an index on a field of type "MONEY"? ----------------------------- LONG VERSION: I have a table with a field of type "money". I very often need to access...
20
by: Hemant Shah | last post by:
Folks, I am using DB2 UDB 8.2 on AIX 5.1. How large of a bufferpool can you create? I tried to create a 4GB bufferpool db2 complained that is cannot allocate enogth memory. I have 16GB on this...
2
by: Just D. | last post by:
All, Do we have a simple way to Create an object on the fly knowing just an object type? The usual design-time way is to write a code something like this: CObjectType obj = new CObjectType();...
0
by: BRINER Cedric | last post by:
Synopsis CREATE { TEMPORARY | TEMP } ] TABLE /table_name/ ( { /column_name/ /data_type/ ] | /table_constraint/ } ) ) ] ... ...
17
by: Jeffrey W. Baker | last post by:
Greetings, I have a 23GB data table upon which I am building a primary key of three columns. The data is mounted in a 137GB device and pg_xlog is mounted on a separate 3.5GB device. I have...
4
by: Ying Lu | last post by:
Hello, I have a table named "USER" under MySQL database. When I am trying to move tables from MySQL to PostgreSQL, I found that I could not create a table namely "USER". I guess "USER" is a key...
7
by: MarkoH | last post by:
Wsdl.exe /server creates abstract class derived from WebService. Is there a way to create this class at runtime based on some WSDL file given at runtime ? What would be even better - creating...
4
by: Sathyaish | last post by:
The WebRequest class implements IWebRequestCreate and hence, a method Create. This method has two other overloads, one of which is a private method. Here's how it looks: public static...
1
by: orenbt78 | last post by:
Hi, I am trying to: 1. Create a SQL database (I am working with SQL 2005 Express) 2. with a C# code 3. when the user is not the computer administrator. I have managed to create the database...
3
by: gillian3114 | last post by:
i got 3 type username and password : 1. boss 2. manager 3. staff how should i check if the username is exist then pop up msgbox error else add username and password to database
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.