Concatinating records 2

[bikerboy718]

Is there a way to concatenate records? What I am trying to do is add on to a field in a recordset with the following record that has the same ordernumber. For example:

TableA
ID OrderNumber Comments
1 1000 My name is
2 2000 Today is the 10th
3 1000 Michael

Is there a way to output the following results for Order number 1000

ID OrderNumber Comments
1 1000 My name is Michael

Any help is greatly appreciated.

With Great Power Comes Great Responsibility!!!

Michael

 

[gmmastros]

This will only work the way you want it to when you are filtering on order number.

Declare @Result VarChar(100)

Select  @Result = IsNull(@Result + ' ', IsNull(@Result, '')) + Comments
From	TableA
Where	Comments Is Not NULL
		And OrderNumber = 1000

Select @Result

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

You can try:

select ID, OrderNumber,Comments = coalesce(Comments ' ', '') + convert(varchar(20), Comments)
From TableA
where OrderNumber = 1000
group by ID, OrderNumber,  coalesce(Comments ' ', '') + convert(varchar(20), Comments)

Jim

 

[SQLDenis]

create table TableA (id int identity,ordernumber int,comments varchar(50))

insert into tableA

select 1000, 'My name is' union all
select 2000, 'Today is the 10th' union all
select 1000, 'Michael'


select a1.comments + ' ' + a2.comments
from tableA a1 join tableA a2 on a1.ordernumber = a2.ordernumber
and a1.id < a2.id
--where clause optional in this case only 1 record
-- do a left join with coalesce arounf a2 if you want all records

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bikerboy718]

Jim (jbenson001) your code produces the following results:

1000 My name isMy name is
1000 MichaelMichael.


With Great Power Comes Great Responsibility!!!

Michael

 

[gmmastros]

Denis's solution only works if there are 2 records with the same order number. By making it a left join and adding isnull checks, you can get it to work with only 1 record (i.e. OrderNumber = 2000). However, it will not work if there are more than 2 records.

If you use the code I posted before, it won't matter how many records there are.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bikerboy718]

George,

Your solution works perfectly. I was just wondering how the solution that jbenson001 worked. The posting that Denis posted is good but I did not want to create a temp table to bring together the 2 records. Thanks everyone for all of your help.

Michael

With Great Power Comes Great Responsibility!!!

Michael