CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005
user3015739
user3015739

Reputation: 643

Concat rows into 1 string

I am using SQL Server 2005 and would like to find an easier way to concat multiple rows into 1 string.

PK Column1, Column2
--  -------  -------
PK1 apple    orange
PK1 pear     banana
PK1 honey
PK2 apple2    orange2
PK2 pear2     banana2
PK2 honey2

Results : 

PK1, apple orange pear banana honey
PK2, apple2 orange2 pear2 banana2 honey2

It is very easy to use COALESCE but it is not available in SQL Server 2005. I tried XML Path but it appends additional character to the end.

Could you please provide me some suggestion ? Thanks.

Upvotes: 0

Views: 715

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 82010

This will trap null or empty values

Declare @YourTable table (PK int,Column1 varchar(25), Column2 varchar(25))
Insert Into @YourTable values
(1,'apple','orange'),
(1,'pear','banana'),
(1,'honey', null),
(2,'apple2','orange2'),
(2,'pear2','banana2'),
(2,'honey2', null)

Select PK
      ,DelimString = Stuff((Select case when Column1 is null or Column1='' then '' else ' ' + replace(Column1,char(13),'') end
                                  +case when Column2 is null or Column2='' then '' else ' ' + replace(Column2,char(13),'') end  
                             From  @YourTable
                             Where PK=A.PK
                             For XML  Path('')), 1, 1, '') 
 From @YourTable A
 Group By PK

Returns

PK  DelimString
1   apple orange pear banana honey 
2   apple2 orange2 pear2 banana2 honey2

Upvotes: 0

shA.t
shA.t

Reputation: 16968

When your data is some blank spaces ' ' in result of for xml path you will see a   at the end like '  ':

select '    ' for xml path ('');

In your case I can use this query:

select t.PK, 
    ltrim(rtrim(replace(
    (select ' ' + isnull(ti.Column1, '') + ' ' + isnull(ti.Column2, '')
     from yourTable ti 
     where ti.PK = t.PK
     for xml path (''))
     , ' ', ''))) fruits
from yourTable t
group by t.PK;

Upvotes: 0

Fandango68
Fandango68

Reputation: 4898

A far easier solution is to use XML PATH

SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(a.Column1)
FROM dbo.mytbl AS a WHERE a.ColumnX = somecondition
FOR XML PATH ( '' ) , TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SELECT @cols

Of course the WHERE clause is optional in your case. And without spoon feeding you, apply the same to your other column and concatenate them. Voila!

Upvotes: 1

Related Questions