473,320 Members | 2,145 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,320 software developers and data experts.

Help - how to concatinate strings from multiple rows?

I have a need to concatenate all Descriptions from a select statement
SELECT
t_ReviewSection.PeerRevSectionDescription
FROM
t_ReviewSection
WHERE
t_ReviewSection.PeerRevID = @lngRevID
ORDER BY
t_ReviewSection.PeerRevSectionOrder
I want to return a single string "section1, section2, section3, section4"
based on the multiple rows returned.

Any ideas
Jul 20 '05 #1
5 14559
Jerry (je************@ptd.net) writes:
I have a need to concatenate all Descriptions from a select statement
SELECT
t_ReviewSection.PeerRevSectionDescription
FROM
t_ReviewSection
WHERE
t_ReviewSection.PeerRevID = @lngRevID
ORDER BY
t_ReviewSection.PeerRevSectionOrder
I want to return a single string "section1, section2, section3, section4"
based on the multiple rows returned.


There is unfortunately no safe way to do this with a single SELECT
statement. The only safe way is to iterate over the data in a cursor
and concatenate to a variable.

It may be better to just get the data from SQL Server and then concatenate
in the client.
--
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
Thanks - I did it in code as you suggested. I always have that to fall back
on but you know how it is. You try to do everyting in the Sproc if you can
and I'm not nearly as talented in Sprocs as I am in VB. Figured maybe I was
missing something.

Thanks

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn*********************@127.0.0.1...
Jerry (je************@ptd.net) writes:
I have a need to concatenate all Descriptions from a select statement
SELECT
t_ReviewSection.PeerRevSectionDescription
FROM
t_ReviewSection
WHERE
t_ReviewSection.PeerRevID = @lngRevID
ORDER BY
t_ReviewSection.PeerRevSectionOrder
I want to return a single string "section1, section2, section3, section4" based on the multiple rows returned.


There is unfortunately no safe way to do this with a single SELECT
statement. The only safe way is to iterate over the data in a cursor
and concatenate to a variable.

It may be better to just get the data from SQL Server and then concatenate
in the client.
--
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 #3
Jerry (je************@ptd.net) writes:
Thanks - I did it in code as you suggested. I always have that to fall
back on but you know how it is. You try to do everyting in the Sproc
if you can and I'm not nearly as talented in Sprocs as I am in VB.
Figured maybe I was missing something.


What to do in application code and what to do in SQL may not always
be obvious. But as a general rule of thumb, SQL is good for raw data
retrieval, and also business logic and also computations to some degree.
However, string handling and formatting is poor in SQL.
--
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
JK
Concatenation of data in different rows can be done without using a cursor..
try this out...
DECLARE @desc VARCHAR(1000)
SELECT @desc =@desc + ', ' +
t_ReviewSection.PeerRevSectionDescription
FROM
t_ReviewSection
WHERE
t_ReviewSection.PeerRevID = @lngRevID
ORDER BY
t_ReviewSection.PeerRevSectionOrder
SET @Desc=substring(@Desc,3,len(@Desc))

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...
Jerry (je************@ptd.net) writes:
Thanks - I did it in code as you suggested. I always have that to fall
back on but you know how it is. You try to do everyting in the Sproc
if you can and I'm not nearly as talented in Sprocs as I am in VB.
Figured maybe I was missing something.


What to do in application code and what to do in SQL may not always
be obvious. But as a general rule of thumb, SQL is good for raw data
retrieval, and also business logic and also computations to some degree.
However, string handling and formatting is poor in SQL.

Jul 20 '05 #5
JK (ja**************@hotmail.com) writes:
Concatenation of data in different rows can be done without using a
cursor.. try this out...
DECLARE @desc VARCHAR(1000)
SELECT @desc =@desc + ', ' +
t_ReviewSection.PeerRevSectionDescription
FROM
t_ReviewSection
WHERE
t_ReviewSection.PeerRevID = @lngRevID
ORDER BY
t_ReviewSection.PeerRevSectionOrder
SET @Desc=substring(@Desc,3,len(@Desc))


But it is not realiable. The result of the above operation is undefined,
so you may what you expect, or you may get something else.

See http://support.microsoft.com/default.aspx?scid=287515.

--
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 #6

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

Similar topics

4
by: Matthew Paterson | last post by:
Can anyone tell me why in the code below the sql execute that uses $sql99 does not work while the one with $sql2 works. $sql99 is exactly the same as $sql2 when printed out. I get an error...
3
by: Keith Chadwick | last post by:
We current have a bunch of web services that make user of the SQLXML object. A template is created in code which calls several stored procedures each of which returns multiple xml recordsets from...
9
by: Dr. StrangeLove | last post by:
Greetings, Let say we want to split column 'list' in table lists into separate rows using the comma as the delimiter. Table lists id list 1 aa,bbb,c 2 e,f,gggg,hh 3 ii,kk 4 m
2
by: Daniel | last post by:
I'm new to .Net and all of its abilities so I hope this makes sense. Basically I'm confused on when is the appropriate time to use web forms controls vs. regular HTML. For example in ASP...
1
by: Rahul | last post by:
Hi Everybody I have some problem in my script. please help me. This is script file. I have one *.inq file. I want run this script in XML files. But this script errors shows . If u want i am...
15
by: Jay | last post by:
I have a multi threaded VB.NET application (4 threads) that I use to send text messages to many, many employees via system.timer at a 5 second interval. Basically, I look in a SQL table (queue) to...
4
by: Christian Maier | last post by:
Hi After surfing a while I have still trouble with this array thing. I have the following function and recive a Segmentation fault, how must I code this right?? Thanks Christian Maier
6
navanova
by: navanova | last post by:
can any body tell me how to concatinate multiple lines of strings in vb.net? Thank you
1
by: deepaks85 | last post by:
Dear All, I want to send some data through a form with Multiple attachment in an HTML Format. I have tried it but it is not working for me. I am able to send data without attachment but with the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.