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 ideasJerry (jerryg_no_spam@.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, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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" <sommar@.algonet.se> wrote in message
news:Xns94EE134E26DDYazorman@.127.0.0.1...
> Jerry (jerryg_no_spam@.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, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Jerry (jerryg_no_spam@.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, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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 <sommar@.algonet.se> wrote in message news:<Xns94EEF2D071E89Yazorman@.127.0.0.1>...
> Jerry (jerryg_no_spam@.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.|||JK (jaikrishnan_nair@.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, sommar@.algonet.se

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