Help - how to concatinate strings from multiple rows?

[Jerry]

I have a need to concatenate all Descriptions from a select statement


SELECT
t_ReviewSection .PeerRevSection Description
FROM
t_ReviewSection
WHERE
t_ReviewSection .PeerRevID = @lngRevID
ORDER BY
t_ReviewSection .PeerRevSection Order


I want to return a single string "section1, section2, section3, section4"
based on the multiple rows returned.

Any ideas

[Erland Sommarskog]

Re: Help - how to concatinate strings from multiple rows?

Jerry (jerryg_no_spam @ptd.net) writes:[color=blue]
> I have a need to concatenate all Descriptions from a select statement
>
>
> SELECT
> t_ReviewSection .PeerRevSection Description
> FROM
> t_ReviewSection
> WHERE
> t_ReviewSection .PeerRevID = @lngRevID
> ORDER BY
> t_ReviewSection .PeerRevSection Order
>
>
> I want to return a single string "section1, section2, section3, section4"
> based on the multiple rows returned.[/color]

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/techinfo/productdoc/2000/books.asp

[Jerry]

Re: Help - how to concatinate strings from multiple rows?

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:Xns94EE134 E26DDYazorman@1 27.0.0.1...[color=blue]
> Jerry (jerryg_no_spam @ptd.net) writes:[color=green]
> > I have a need to concatenate all Descriptions from a select statement
> >
> >
> > SELECT
> > t_ReviewSection .PeerRevSection Description
> > FROM
> > t_ReviewSection
> > WHERE
> > t_ReviewSection .PeerRevID = @lngRevID
> > ORDER BY
> > t_ReviewSection .PeerRevSection Order
> >
> >
> > I want to return a single string "section1, section2, section3,[/color][/color]
section4"[color=blue][color=green]
> > based on the multiple rows returned.[/color]
>
> 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/techinf...2000/books.asp[/color]

[Erland Sommarskog]

Re: Help - how to concatinate strings from multiple rows?

Jerry (jerryg_no_spam @ptd.net) writes:[color=blue]
> 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.[/color]

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/techinfo/productdoc/2000/books.asp

[JK]

Re: Help - how to concatinate strings from multiple rows?

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 .PeerRevSection Description
FROM
t_ReviewSection
WHERE
t_ReviewSection .PeerRevID = @lngRevID
ORDER BY
t_ReviewSection .PeerRevSection Order
SET @Desc=substring (@Desc,3,len(@D esc))



Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns94EEF2 D071E89Yazorman @127.0.0.1>...[color=blue]
> Jerry (jerryg_no_spam @ptd.net) writes:[color=green]
> > 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.[/color]
>
> 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.[/color]

[Erland Sommarskog]

Re: Help - how to concatinate strings from multiple rows?

JK (jaikrishnan_na ir@hotmail.com) writes:[color=blue]
> 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 .PeerRevSection Description
> FROM
> t_ReviewSection
> WHERE
> t_ReviewSection .PeerRevID = @lngRevID
> ORDER BY
> t_ReviewSection .PeerRevSection Order
> SET @Desc=substring (@Desc,3,len(@D esc))[/color]

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/techinfo/productdoc/2000/books.asp