Showing posts with label concatnate. Show all posts
Showing posts with label concatnate. Show all posts

Tuesday, February 14, 2012

concatnate values for different rows

Hi ,

I have a situation where i need to concatnate values from different rows and store it a one string.

sample

dealid date

1 1/5/2007

1 2/4/2009

2 5/5/2004

2 8/5/2006

2 4/8/2006

so for one particular deal how many ever dates there are , i need to concatnate them all separated by a comma(,) and return and one string.

Is ther any way i could do it, Any suggestions appreaciated

Thanks

Ashsih

Maybe something like:

select distinct dealId,
reverse(substring(reverse(
( select convert(varchar(10), date, 101) + ', ' as [text()]
from theTable b
where a.dealId = b.dealId

order by date desc
for xml path('')
)), 3, 300))
from theTable a

|||

For a particular DealD:

declare @.targetDeal int set @.targetDeal = 1

select reverse(substring(reverse

( select convert(varchar(10), date, 101) + ', ' as [text()]
from theTable

where dealId = @.targetDeal

order by date desc
for xml path('')

), 3, 300)) as DealDates

concatnate multiple results into one field

Hi
I am trying to query a table and return the results all in one field.
My table basicly looks like this:
tblBody:
ID Body
1 This is body 1
2 This is body 2
3 This is body 3
Now i want to select all this data, and concatenate it into one field
So when i do:
SELECT Body FROM tblBody (altered to be the right way of doing this of
course)
The results are:
Body
This is body 1This is body 2This is body 3
and not
Body
1 This is Body 1
2 This is Body 2
3 This is Body 3
TIAhttp://www.aspfaq.com/show.asp?id=2529
"Grant Merwitz" wrote:
> Hi
> I am trying to query a table and return the results all in one field.
> My table basicly looks like this:
> tblBody:
> ID Body
> 1 This is body 1
> 2 This is body 2
> 3 This is body 3
> Now i want to select all this data, and concatenate it into one field
> So when i do:
> SELECT Body FROM tblBody (altered to be the right way of doing this of
> course)
> The results are:
> Body
> This is body 1This is body 2This is body 3
> and not
> Body
> 1 This is Body 1
> 2 This is Body 2
> 3 This is Body 3
> TIA
>
>|||Thanks, that was exactly what i was looking for.
But now i've realised another problem:
The fields i'm trying to join are all of varchar(4000)
So i don't believe there's a variable i can store these in to return in a
Sql query.
Is there?
I may have to return multiple rows and join them in my business layer.
Thanks for you help, any thoughts here?
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:63B64C15-2098-4802-B189-60EF51BA668E@.microsoft.com...
> http://www.aspfaq.com/show.asp?id=2529
>
> "Grant Merwitz" wrote:
>> Hi
>> I am trying to query a table and return the results all in one field.
>> My table basicly looks like this:
>> tblBody:
>> ID Body
>> 1 This is body 1
>> 2 This is body 2
>> 3 This is body 3
>> Now i want to select all this data, and concatenate it into one field
>> So when i do:
>> SELECT Body FROM tblBody (altered to be the right way of doing this
>> of
>> course)
>> The results are:
>> Body
>> This is body 1This is body 2This is body 3
>> and not
>> Body
>> 1 This is Body 1
>> 2 This is Body 2
>> 3 This is Body 3
>> TIA
>>

concatnate multiple results into one field

Hi
I am trying to query a table and return the results all in one field.
My table basicly looks like this:
tblBody:
ID Body
1 This is body 1
2 This is body 2
3 This is body 3
Now i want to select all this data, and concatenate it into one field
So when i do:
SELECT Body FROM tblBody (altered to be the right way of doing this of
course)
The results are:
Body
This is body 1This is body 2This is body 3
and not
Body
1 This is Body 1
2 This is Body 2
3 This is Body 3
TIAhttp://www.aspfaq.com/show.asp?id=2529
"Grant Merwitz" wrote:

> Hi
> I am trying to query a table and return the results all in one field.
> My table basicly looks like this:
> tblBody:
> ID Body
> 1 This is body 1
> 2 This is body 2
> 3 This is body 3
> Now i want to select all this data, and concatenate it into one field
> So when i do:
> SELECT Body FROM tblBody (altered to be the right way of doing this of
> course)
> The results are:
> Body
> This is body 1This is body 2This is body 3
> and not
> Body
> 1 This is Body 1
> 2 This is Body 2
> 3 This is Body 3
> TIA
>
>|||Thanks, that was exactly what i was looking for.
But now i've realised another problem:
The fields i'm trying to join are all of varchar(4000)
So i don't believe there's a variable i can store these in to return in a
Sql query.
Is there?
I may have to return multiple rows and join them in my business layer.
Thanks for you help, any thoughts here?
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:63B64C15-2098-4802-B189-60EF51BA668E@.microsoft.com...[vbcol=seagreen]
> http://www.aspfaq.com/show.asp?id=2529
>
> "Grant Merwitz" wrote:
>

concatnate multiple results into one field

Hi
I am trying to query a table and return the results all in one field.
My table basicly looks like this:
tblBody:
ID Body
1 This is body 1
2 This is body 2
3 This is body 3
Now i want to select all this data, and concatenate it into one field
So when i do:
SELECT Body FROM tblBody (altered to be the right way of doing this of
course)
The results are:
Body
This is body 1This is body 2This is body 3
and not
Body
1 This is Body 1
2 This is Body 2
3 This is Body 3
TIA
http://www.aspfaq.com/show.asp?id=2529
"Grant Merwitz" wrote:

> Hi
> I am trying to query a table and return the results all in one field.
> My table basicly looks like this:
> tblBody:
> ID Body
> 1 This is body 1
> 2 This is body 2
> 3 This is body 3
> Now i want to select all this data, and concatenate it into one field
> So when i do:
> SELECT Body FROM tblBody (altered to be the right way of doing this of
> course)
> The results are:
> Body
> This is body 1This is body 2This is body 3
> and not
> Body
> 1 This is Body 1
> 2 This is Body 2
> 3 This is Body 3
> TIA
>
>
|||Thanks, that was exactly what i was looking for.
But now i've realised another problem:
The fields i'm trying to join are all of varchar(4000)
So i don't believe there's a variable i can store these in to return in a
Sql query.
Is there?
I may have to return multiple rows and join them in my business layer.
Thanks for you help, any thoughts here?
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:63B64C15-2098-4802-B189-60EF51BA668E@.microsoft.com...[vbcol=seagreen]
> http://www.aspfaq.com/show.asp?id=2529
>
> "Grant Merwitz" wrote: