Concat rows into 1 string

Question

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.

Fandango68 · Accepted Answer · 2017-02-07 03:02:51Z

1

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!

answered Feb 7, 2017 at 3:02

Fandango68

4,9555 gold badges50 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shA.t · Accepted Answer · 2017-02-07 05:19:51Z

0

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 (''))
     , '&#x20;', ''))) fruits
from yourTable t
group by t.PK;

answered Feb 7, 2017 at 5:19

shA.t

17k5 gold badges59 silver badges121 bronze badges

Comments

John Cappelletti · Accepted Answer · 2017-02-07 15:03:09Z

0

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

edited Feb 7, 2017 at 15:03

answered Feb 7, 2017 at 2:40

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

15 Comments

user3015739 Over a year ago

Thanks John. For some reason, using XML Path, it appended &#x0D to the end of the string. Will see how I can remove it.

John Cappelletti Over a year ago

@user3015739 That is a line feed

user3015739 Over a year ago

I am trying to figure out where to put the TEXT command per what described here : stackoverflow.com/questions/16547175/…

user3015739 Over a year ago

Yeah, I can use a REPLACE function to remove it. Now, I added some new tweak where I need another column in front.

user3015739 Over a year ago

Cappeletti, you are excellent. I will tweak it a bit to differentiate my PK column. Thanks.

|

Collectives™ on Stack Overflow

Concat rows into 1 string

3 Answers 3

Comments

Comments

15 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

15 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related