Prev: Capturing First Error Message
Next: kb923247
From: amj1020 on 21 Jan 2010 09:29
I have a table that will contain a chargekey and a clientkey

Table Temp(
Chargekey int,
Clientkey int)


When I select from the table I will get
chargekey Clientkey
6233 459
6245 459
6589 459

I want to insert the chargekeys to a comments field in another table
and I want it to look like

"Charge ticket 6233,6245,6589"

How can I do this?
From: Tom Cooper on 21 Jan 2010 09:54
One way:

Create Table #Temp(
Chargekey int,
Clientkey int);

Create Table #Result(
Clientkey int,
Comments varchar(200));

Insert #Temp(Chargekey, Clientkey)
Select 6233, 459
Union All Select 6245, 459
Union All Select 6589, 459;

Select * From #Temp;

Insert #Result(Clientkey, Comments)
Select t1.Clientkey,
'Charge ticket ' + SubString((Select ',' + Cast(t2.Chargekey As
varchar(10))
From #Temp t2
Where t1.ClientKey = t2.Clientkey
Order By t2.Chargekey
For XML Path ('')), 2, 200) As Comments
From #Temp t1
Group By t1.Clientkey;

Select * From #Result;

go
Drop Table #Result;
Drop Table #Temp;

See http://www.projectdmx.com/tsql/rowconcatenate.aspx for a discussion of
other ways to do this.

Tom

"amj1020" <angelicred19(a)hotmail.com> wrote in message
news:137ec6b2-408f-435e-9b83-2f8284341299(a)m26g2000yqb.googlegroups.com...
>I have a table that will contain a chargekey and a clientkey
>
> Table Temp(
> Chargekey int,
> Clientkey int)
>
>
> When I select from the table I will get
> chargekey Clientkey
> 6233 459
> 6245 459
> 6589 459
>
> I want to insert the chargekeys to a comments field in another table
> and I want it to look like
>
> "Charge ticket 6233,6245,6589"
>
> How can I do this?

From: Plamen Ratchev on 21 Jan 2010 09:54
Here are a few methods to accomplish that:

http://www.projectdmx.com/tsql/rowconcatenate.aspx

--
Plamen Ratchev
http://www.SQLStudio.com
From: amj1020 on 21 Jan 2010 10:10
On Jan 21, 8:54 am, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
> Here are a few methods to accomplish that:
>
> http://www.projectdmx.com/tsql/rowconcatenate.aspx
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com


Thank you!!
 | 
Pages: 1
Prev: Capturing First Error Message
Next: kb923247