concatenating multiple results to a string?

I am not sure if this is possible but here is what i need. Is it possible to
query the database and concatenate the results into a string? for instance i
f
i said SELECT ORDER_ID FORM CUSTOMER_ORDERS WHERE CUSTOMER = 'CUSTOMER A'
and it returned order1,order2,order3,order4 - the returned values are what i
needed concatenated as on string.
Regards,
chrisThis is a common newbie mistake, from working with file systems that
were part of the application language. In a tiered architecture,
display functions are done in the front end and not in the database.
The kludge, if you do not want to be a good programmer, is to use a
cursor to concatenate the string. Slow, not portable and something of
a probelm to maintain, but it will work.|||You can pull the data into an Excel pivot table.
"chris" <chris@.discussions.microsoft.com> wrote in message
news:A015A4BE-A46E-40B1-933F-4787A73E9267@.microsoft.com...
> I am not sure if this is possible but here is what i need. Is it possible
to
> query the database and concatenate the results into a string? for instance
if
> i said SELECT ORDER_ID FORM CUSTOMER_ORDERS WHERE CUSTOMER = 'CUSTOMER A'
> and it returned order1,order2,order3,order4 - the returned values are what
i
> needed concatenated as on string.
> Regards,
> chris|||I realize it should be handled on the front end but thats not possible. My
company has an ERP system that uses an outdated report writer. It will allow
me to do an "extended query" to the database for one record only. I was
hoping to concatenate my returned rows by exec a SP and pulling that into th
e
report.
"--CELKO--" wrote:

> This is a common newbie mistake, from working with file systems that
> were part of the application language. In a tiered architecture,
> display functions are done in the front end and not in the database.
> The kludge, if you do not want to be a good programmer, is to use a
> cursor to concatenate the string. Slow, not portable and something of
> a probelm to maintain, but it will work.
>|||See if this helps:
http://groups-beta.google.com/group...
5bf366dd9e73e
AMB
"chris" wrote:

> I am not sure if this is possible but here is what i need. Is it possible
to
> query the database and concatenate the results into a string? for instance
if
> i said SELECT ORDER_ID FORM CUSTOMER_ORDERS WHERE CUSTOMER = 'CUSTOMER A'
> and it returned order1,order2,order3,order4 - the returned values are what
i
> needed concatenated as on string.
> Regards,
> chris|||Let's assume you have a query with multiple records for each employee (one
record for each phone number):
Fred 555-555-0235
Fred 555-555-9124
Sue 555-555-0133
Now you want to roll up all the employee records so that that the phone
numbers are a comma delimited list like so:
Fred 555-555-0235, 555-555-9124
Sue 555-555-0133
You can select the employee list into a temporary table and then select a
distinct employee list with null PhoneList into a 2nd temporary table. Once
done, use a cursor to loop through the 1st table and update the PhoneList
column on the 2nd table using the EmployeeID. If anyone knows of a set based
query / update that will achieve the same then please post. Otherwise, don't
knock it.
declare Employees cursor for
select
EmployeeID,
Phone
from
#employees
open Employees
fetch Employees into
@.EmployeeID,
@.Phone
while (@.@.fetch_status = 0)
begin
update
#PhoneCombined
set
PhoneList = PhoneList +
case
when PhoneList = '' then @.Phone
when PhoneList like '%' + @.Phone + '%' then ''
else ', ' + @.Phone
end
where
EmployeeID = @.EmployeeID
fetch Employees into
@.EmployeeID,
@.Phone
end
close Employees
deallocate Employees
drop table #employees
"chris" <chris@.discussions.microsoft.com> wrote in message
news:A015A4BE-A46E-40B1-933F-4787A73E9267@.microsoft.com...
> I am not sure if this is possible but here is what i need. Is it possible
to
> query the database and concatenate the results into a string? for instance
if
> i said SELECT ORDER_ID FORM CUSTOMER_ORDERS WHERE CUSTOMER = 'CUSTOMER A'
> and it returned order1,order2,order3,order4 - the returned values are what
i
> needed concatenated as on string.
> Regards,
> chris|||Test this:
DECLARE @.v CHAR(255)
SET @.v = ''
SELECT @.v = LTRIM(RTRIM(@.v)) + CONVERT(CHAR, Order_Id) FROM Customer_Orders
WHERE Customer = 'Customer A'
SELECT @.v
GO
Mihaly
"chris" wrote:

> I am not sure if this is possible but here is what i need. Is it possible
to
> query the database and concatenate the results into a string? for instance
if
> i said SELECT ORDER_ID FORM CUSTOMER_ORDERS WHERE CUSTOMER = 'CUSTOMER A'
> and it returned order1,order2,order3,order4 - the returned values are what
i
> needed concatenated as on string.
> Regards,
> chris|||Here is other method
Declare @.s nvarchar(255)
select
@.s=coalesce(@.s+','+convert(varchar(10),o
rder_Id),convert(varchar(10),order_I
d))
from Customers_Orders
Where Customer = 'Customer A'
select @.s
Madhivanan|||And what if the result is expected to be very long (too long for
nvarchar)? I understand I can't declare a TEXT variable
madhivanan2001@.gmail.com wrote:
> Here is other method
> Declare @.s nvarchar(255)
> select
> @.s=coalesce(@.s+','+convert(varchar(10),o
rder_Id),convert(varchar(10),order
_Id))
> from Customers_Orders
> Where Customer = 'Customer A'
> select @.s
> Madhivanan
>|||All the more reason to handle the concatenation where it belongs: one the
client/presentation tier.
On 2/27/05 10:55 AM, in article OaoWUROHFHA.1392@.TK2MSFTNGP10.phx.gbl, "Uri
Dor" <tablul@.newsgroups.nospam> wrote:
> And what if the result is expected to be very long (too long for
> nvarchar)? I understand I can't declare a TEXT variable
> madhivanan2001@.gmail.com wrote: