Concatenate All values to one select

I'm trying to get all the values in one field
i.e from Northwind
Select ShipName from orders
where ShippedDate = Cast(GetDate() as Varchar(11)
I need the output to be like as one value with char(13) + char(10)
Vins et als Chevalier
Toms Spezialitten
Hanari Carnes
Victuailles en stock
Suprmes dlices
Hanari Carnes
Chop-suey Chinese
Richter Supermarkt
Wellington Importadora
HILARION-Abastos
Ernst Handel
ect ...
Thanks
Stephen K. MiyasatoSee if this helps.
http://milambda.blogspot.com/2005/0...s-as-array.html
AMB
"Stephen K. Miyasato" wrote:

> I'm trying to get all the values in one field
>
> i.e from Northwind
> Select ShipName from orders
> where ShippedDate = Cast(GetDate() as Varchar(11)
> I need the output to be like as one value with char(13) + char(10)
> Vins et als Chevalier
> Toms Spezialit?ten
> Hanari Carnes
> Victuailles en stock
> Suprêmes délices
> Hanari Carnes
> Chop-suey Chinese
> Richter Supermarkt
> Wellington Importadora
> HILARION-Abastos
> Ernst Handel
> ect ...
> Thanks
> Stephen K. Miyasato
>
>|||You can do something like this:
declare @.res nvarchar(4000)
set @.res = ''
select @.res = @.res + ShipName + char(13) + char(10) from Orders where Shippe
dDate is null
print @.res
Stephen K. Miyasato wrote:
> I'm trying to get all the values in one field
> i.e from Northwind
> Select ShipName from orders
> where ShippedDate = Cast(GetDate() as Varchar(11)
> I need the output to be like as one value with char(13) + char(10)
> Vins et als Chevalier
> Toms Spezialitten
> Hanari Carnes
> Victuailles en stock
> Suprmes dlices
> Hanari Carnes
> Chop-suey Chinese
> Richter Supermarkt
> Wellington Importadora
> HILARION-Abastos
> Ernst Handel
> ect ...
> Thanks
> Stephen K. Miyasato
>|||Stephen K. Miyasato (miyasat@.flex.com) writes:
> I'm trying to get all the values in one field
> i.e from Northwind
> Select ShipName from orders
> where ShippedDate = Cast(GetDate() as Varchar(11)
> I need the output to be like as one value with char(13) + char(10)
> Vins et als Chevalier
> Toms Spezialitten
> Hanari Carnes
> Victuailles en stock
> Suprmes dlices
> Hanari Carnes
> Chop-suey Chinese
> Richter Supermarkt
> Wellington Importadora
> HILARION-Abastos
> Ernst Handel
> ect ...
Which version of SQL Server?
If you are on SQL 2000, you need to run a cursor. (The solutions posted
in the thread relies on undefined behaviour, and could give undesired
results.)
On SQL 2005 you can do as in this example:
select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I tried this on a field with datatype Ntext but could not get it to work.
I also tried cast(memo, VarChar(8000)) which failed to give the right
result.
Any ideas on how to convert ntext to make the solution below to work?
Thanks
Stephen K. Miyasato
"Sericinus hunter" <serhunt@.flash.net> wrote in message
news:e%Q0g.10712$%m4.3143@.newssvr33.news.prodigy.com...
> You can do something like this:
> declare @.res nvarchar(4000)
> set @.res = ''
> select @.res = @.res + ShipName + char(13) + char(10) from Orders where
> ShippedDate is null
> print @.res
> Stephen K. Miyasato wrote:|||Stephen K. Miyasato (miyasat@.flex.com) writes:
> I tried this on a field with datatype Ntext but could not get it to work.
> I also tried cast(memo, VarChar(8000)) which failed to give the right
> result.
> Any ideas on how to convert ntext to make the solution below to work?
So the really column you have is next? And the values are typically > 4000
characters?
This is not going be fun at all. I think my two prime suggestions
would be:
1) Do it client-side
2) Upgrade to SQL 2005 where you can use the new nvarchar(MAX) data type,
which does not have all the restrictions of the ntext data type.
The problem on SQL 2000 is that you cannot work with ntext variables,
so you would have to do this in a table column. And to make it even more
fun, you would have to use the UPDATETEXT command. You can read about it
in Books Online, but you will have to play around a bit to understand
how it works.
Why don't you do describe the actual business problem. Maybe there is a
much better solution?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The field are less then Varchar(4000) and I can use a cast function into a
#temp table
cast(memo as varChar(4000))))AS memo , DateEntered)
How does one iterate through the table looking for a var
DECLARE @.iFoundAt int
set @.iFoundAt = (Select CHARINDEX('Apr 18 2006' ,memo) from Flags where
PatNo = 3760
if @.iFoundAt > 0
BEGIN
-- iterate though table and add records where 'Apr 18 2006' only, found
END
Business problem. I have a list of Clinical reminders with each record
being a reminder.
Description memo DateEntered
Stool 4/18/2006 - Done 4/18/2006
Ha1c 4/16/2006 - 6.5 4/16//2006
I want to query the flags table and insert the text into a word processing
document on the client side.
Thanks
Stephen K. Miyasato
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A9D8AC3D2DEYazorman@.127.0.0.1...
> Stephen K. Miyasato (miyasat@.flex.com) writes:
> So the really column you have is next? And the values are typically > 4000
> characters?
> This is not going be fun at all. I think my two prime suggestions
> would be:
> 1) Do it client-side
> 2) Upgrade to SQL 2005 where you can use the new nvarchar(MAX) data type,
> which does not have all the restrictions of the ntext data type.
> The problem on SQL 2000 is that you cannot work with ntext variables,
> so you would have to do this in a table column. And to make it even more
> fun, you would have to use the UPDATETEXT command. You can read about it
> in Books Online, but you will have to play around a bit to understand
> how it works.
> Why don't you do describe the actual business problem. Maybe there is a
> much better solution?
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks All,
I figured it out
Stephen K. Miyasato
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:OGg6cc5YGHA.1180@.TK2MSFTNGP03.phx.gbl...
> The field are less then Varchar(4000) and I can use a cast function into a
> #temp table
> cast(memo as varChar(4000))))AS memo , DateEntered)
> How does one iterate through the table looking for a var
> DECLARE @.iFoundAt int
> set @.iFoundAt = (Select CHARINDEX('Apr 18 2006' ,memo) from Flags where
> PatNo = 3760
> if @.iFoundAt > 0
> BEGIN
> -- iterate though table and add records where 'Apr 18 2006' only, found
> END
> Business problem. I have a list of Clinical reminders with each record
> being a reminder.
> Description memo DateEntered
> Stool 4/18/2006 - Done 4/18/2006
> Ha1c 4/16/2006 - 6.5 4/16//2006
> I want to query the flags table and insert the text into a word processing
> document on the client side.
> Thanks
> Stephen K. Miyasato
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns97A9D8AC3D2DEYazorman@.127.0.0.1...
>sqlsql