Concatenate field values

Question

Concatenate field values

jamie1984

SSC Rookie

Points: 41
More actions
July 15, 2008 at 9:18 am

#184619

OK, here goes:
there is a 3rd party windows app which uses sql2005 to store data. in the app is a text box which when multiple lines are used is writes them back as individual records.
So, table name - "tblDetails" has following fields:
WoNumber - key
LineNumber - multiples of 10,000 - so line 1 - 10000, line 2 = 20000 etc...
Detail - the text on that line
I am writing a web front end for it. what I want to do is retrieve the record, e.g.
Select [detail] from tblDetails WHERE [WoNumber] = '12345'
But the part i am having difficulty with is I want to join the details from each record into one field, so:
detail+detail+detail+detail
line1+line2+line3+line4
hoping the screenshot will make things clearer
any advice appreciated.
cheers,
jamie.

Viewing 6 posts - 1 through 5 (of 5 total)

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

einman33 SSCrazy Points: 2813 More actions · Answer 1

Do not have you stored proc/data tier wast time doing this. Have the middle tier do this for you. Its not a super efficient process for that tier, but it is a good balancing act for this type of scenario. You may want to look at the PIVOT keyword in SQL 2005 if this must be done in TSQL.

jamie1984 SSC Rookie Points: 41 More actions · Answer 2

Hi Eric, thanks for the reply, can you suggest how you would do this without using T-SQL? I have taken a quick look at PIVOT but doesnt seem the most intuitive to pick up.

thanks,

jamie.

RyanRandall SSChampion Points: 13623 More actions · Answer 3

You can use something like this to append the bits together...

--sample data

declare @t table (WoNumber varchar(20), LineNumber int, Detail varchar(100))

insert @t

select 'SVO0002349', 10000, 'a '

union all select 'SVO0002349', 20000, 'b '

union all select 'SVO0002349', 30000, 'c '

union all select 'SVO0002349', 40000, 'd'

union all select 'x', 10000, 'aaaaa aaaaaa '

union all select 'x', 20000, 'bbb bbb bbb'

--calculation

select WoNumber,

(select '' + Detail from @t where WoNumber = a.WoNumber order by LineNumber for xml path('')) as Detail

from @t a group by WoNumber

/* results

WoNumber Detail

-------------------- ---------------------------------

SVO0002349 a b c d

x aaaaa aaaaaa bbb bbb bbb

*/

I'm with Eric though.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Brian-538429 SSC Veteran Points: 244 More actions · Answer 4

DECLARE @details AS VARCHAR(MAX)

SET @details = ''

DECLARE @lineNumbers AS VARCHAR(MAX)

SET @lineNumbers = ''

SELECT @details = @details + Detail + ', ', @lineNumbers = @lineNumbers + CAST(LineNumber AS VARCHAR) + ', ' FROM tblDetails WHERE WoNumber = '12345'

SELECT @details, @lineNumbers

jamie1984 SSC Rookie Points: 41 More actions · Answer 5

Thanks to everyone for there advice and code samples - I have it working now.

Massive help. Thanks, Jamie.