CHAR column concenation

Question

Post reply

CHAR column concenation

schleep

SSChampion

Points: 12585
More actions
April 8, 2014 at 12:59 pm

#284291

Hey gang,
I'm a little stumped here. I want to concatenate character data from a table.
declare @ttest table
(nItemintegernot null,
ctextvarchar(32)not null)
insert @ttest values (1, 'Row 1 text')
insert @ttest values (1, 'Row 2 text')
insert @ttest values (1, 'Row 3 text')
insert @ttest values (2, 'Row 1 text')
insert @ttest values (2, 'Row 2 text')
insert @ttest values (2, 'Row 3 text')
insert @ttest values (2, 'Row 4 text')
insert @ttest values (2, 'Row 5 text')
I want to get a 2 column result set that looks like
1, 'Row 1 text Row 2 text Row 3 text'
2, 'Row 1 text Row 2 text Row 3 text Row 4 text Row 5 text'
Is this possible w/ out a temp table? I'm desperately trying to avoid the dreaded nested Insert/Exec error.
Thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

there's a neat trick with FOR XML you can use:

declare @ttest table

(nItemintegernot null,

ctextvarchar(32)not null)

insert @ttest values (1, 'Row 1 text')

insert @ttest values (1, 'Row 2 text')

insert @ttest values (1, 'Row 3 text')

insert @ttest values (2, 'Row 1 text')

insert @ttest values (2, 'Row 2 text')

insert @ttest values (2, 'Row 3 text')

insert @ttest values (2, 'Row 4 text')

insert @ttest values (2, 'Row 5 text')

SELECT nItem,stuff(( SELECT ',' + ctext

FROM @ttest s2

WHERE s2.nItem= s1.nItem --- must match GROUP BY below

ORDER BY ctext

FOR XML PATH('')

),1,1,'') as [Itemss]

FROM @ttest s1

GROUP BY s1.nItem --- without GROUP BY multiple rows are returned

ORDER BY s1.nItem

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

schleep SSChampion Points: 12585 More actions · Answer 2

Thank you Lowell, that's brilliant.

I *really* must find some time to bone up on XML.