|
|
concatenating multiple results to a string? |
|
Author |
Message |
yoyo2000

|
Posted: Thu Feb 24 11:57:58 CST 2005 |
Top |
SQL Server Developer >> 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 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
SQL Server298
|
|
|
|
 |
--CELKO--

|
Posted: Thu Feb 24 11:57:58 CST 2005 |
Top |
SQL Server Developer >> concatenating multiple results to a string?
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.
|
|
|
|
 |
JohnnyAppleseed

|
Posted: Thu Feb 24 12:01:23 CST 2005 |
Top |
SQL Server Developer >> concatenating multiple results to a string?
You can pull the data into an Excel pivot table.
> 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
|
|
|
|
 |
chris

|
Posted: Thu Feb 24 12:11:06 CST 2005 |
Top |
SQL Server Developer >> concatenating multiple results to a string?
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 the
report.
> 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.
>
>
|
|
|
|
 |
AlejandroMesa

|
Posted: Thu Feb 24 12:13:02 CST 2005 |
Top |
SQL Server Developer >> concatenating multiple results to a string?
See if this helps:
http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/msg/2d85bf366dd9e73e
AMB
> 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
|
|
|
|
 |
JohnnyAppleseed

|
Posted: Thu Feb 24 13:49:36 CST 2005 |
Top |
SQL Server Developer >> concatenating multiple results to a string?
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
begin
update
#PhoneCombined
set
PhoneList = PhoneList +
case
end
where
fetch Employees into
@EmployeeID,
end
close Employees
deallocate Employees
drop table #employees
> 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
|
|
|
|
 |
Mihaly

|
Posted: Thu Feb 24 14:55:03 CST 2005 |
Top |
SQL Server Developer >> concatenating multiple results to a string?
Test this:
WHERE Customer = 'Customer A'
GO
Mihaly
> 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
|
|
|
|
 |
madhivanan2001

|
Posted: Fri Feb 25 04:14:15 CST 2005 |
Top |
SQL Server Developer >> concatenating multiple results to a string?
Here is other method
select
from Customers_Orders
Where Customer = 'Customer A'
Madhivanan
|
|
|
|
 |
Uri

|
Posted: Sun Feb 27 09:55:09 CST 2005 |
Top |
SQL Server Developer >> concatenating multiple results to a string?
And what if the result is expected to be very long (too long for
nvarchar)? I understand I can't declare a TEXT variable
> Here is other method
>
> select
> from Customers_Orders
> Where Customer = 'Customer A'
>
> Madhivanan
>
|
|
|
|
 |
Aaron

|
Posted: Sun Feb 27 10:58:00 CST 2005 |
Top |
SQL Server Developer >> concatenating multiple results to a string?
All the more reason to handle the concatenation where it belongs: one the
client/presentation tier.
> And what if the result is expected to be very long (too long for
> nvarchar)? I understand I can't declare a TEXT variable
>
>> Here is other method
>>
>> select
>> ))
>> from Customers_Orders
>> Where Customer = 'Customer A'
>>
>> Madhivanan
>>
|
|
|
|
 |
moondaddy

|
Posted: Thu Mar 31 00:33:35 CST 2005 |
Top |
SQL Server Developer >> concatenating multiple results to a string?
This looks very useful. From time to time I need to do a similar thing
where rolling up data isn't practical to do client side for one reason or
another. However, in all of my cases, I need include this rolled up data as
part of a result set. For example: your example below rolls up the Order
Ids for one customer. but how could you do this for all customers where the
resultset would have one column for the customer name and another column for
the concatenated order Ids of each customer?
Any help anyone could offer would be great!
Thanks.
--
>
> Here is other method
>
> select
> from Customers_Orders
> Where Customer = 'Customer A'
>
> Madhivanan
>
|
|
|
|
 |
|
|