How to concatenate two character values in a table into one string

[mkw77]

I am using SQLSERVER 2005

Can anyone help to concatenate character values into one string where the Field1 has the same reference. Output from one of my tmp tables looks like this:-

Field1 field2
32061350 Gas Works
32071470 Boiler Room
36010060 Boiler Room
36031890 Support Agency
40012540 Support Agency
40012540 Staff Needed
40030560 Gas Works
40080070 Staff Needed
40080070 Outside Gas
40080070 Boiler Room

I want the output to show as:-

Field1 Field2
32061350 Gas Works
32071470 Boiler Room
36010060 Boiler Room
36031890 Support Agency
40012540 Support Agency - Staff Needed
40030560 Gas Works
40080070 Staff Needed - Outside Gas - Boiler Room

The can be many field2 to field1.

Hope someone can help

 

[RyanEK]

Hi,

Try the following:

declare @tmp table (field1 int, field2 varchar(20))
insert into @tmp values (32071470,    'Boiler Room')
insert into @tmp values (36010060,    'Boiler Room')
insert into @tmp values (36031890,    'Support Agency')
insert into @tmp values (40012540,    'Support Agency')
insert into @tmp values (40012540,    'Staff Needed')
insert into @tmp values (40012540,    'Third Test')
insert into @tmp values (40030560,    'Gas Works')

select field1,
replace(replace ((  
  select replace(field2,' ','*') as 'data()'  
  from @tmp t2  
  where t1.field1 = t2.field1 
  for xml path('')),' ',' - '),
'*',' ') as Field2
from @tmp t1
group by field1

Ryan

 

[mkw77]

Hi Ryan,

Thanks for your quick response, but I am new to SQLSERVER 2005 and so not sure what you have done, but I can not possibly insert every value, the values are only held in a tmp table. To carry out an insert for every value I would need to hard code all the values for this unless I am not understanding you. Can you explain a little more please.
Cheers

 

[RyanEK]

I created the @tmp table just to show you how the code would work. Just replace @tmp with your existing table.

 

[mkw77]

Hi - I've managed to do with the following:-

;WITH redflags(field1,field2) AS
(SELECT A.field1,
(SELECT B.field2 + ' - ' FROM #tmpflag B WHERE B.field1=A.field2 FOR XML PATH(''))
'Concatinated'
FROM #tmpflag A
GROUP BY A.field1
)
SELECT field1 'PLACE REF',LEFT(field2,LEN(field2)-1) 'REDFLAG' FROM redflags
GO

This gives me output like this:-
Field1 Field2
40012540 Support Agency - Staff Needed
40030560 Gas Works
40080070 Staff Needed - Outside Gas - Boiler Room

Do you know how I can output this as a csv file.

 

[RyanEK]

Nice solution. For outputting to a csv see here Creating CSV files