How to concatenate row values?

Question

Post reply

How to concatenate row values?

MissyDaisy

Ten Centuries

Points: 1164
More actions
June 16, 2009 at 11:32 am

#133004

I am looking for suggestions/ideas to concatenate row values
without using a cursor or CLR.
Basically, I will take row values from Table 1 and concatenate them to store on Table 2.
Table 1
ColCustomerid int
Col 1 char(79)
Col 2 seqno int
Table 2;
ColCustomerid int
ColA varchar(max)
On table 1 , the number of items(seqno) is variable from one customer to another.
Sample Data:
CustID Col1 Col2
11 Line 1 1
11 Line 2 2
11 Line 3 3
11 Line 4 4
22 Line A 1
22 Line B 2
33 Sample Text 1
Desired output
CustID ColA
11 Line 1 Line 2 Line 3 Line 4
22 Line A Line B
33 Sample Text
Thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Try this, see if it does what you need:

select distinct CustID,

(select Col1 + ' '

from Table1 T1_Sub

where CustID = Table1.CustID

order by Col2

for XML path('')) as ColA

from Table1;

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

MissyDaisy Ten Centuries Points: 1164 More actions · Answer 2

Thanks, GSquared. It seems to add some extra characters when concatenating. I only want a white space between the lines but it appears that it adds something else.

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 3

MissDaisy,

It's normally a very bad idea to store concatenated data in a table so I'm curious... why do you want to store concatenated data in a table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MissyDaisy Ten Centuries Points: 1164 More actions · Answer 4

The concatenated string is a comment. The comment is about machines. There is already a machine table and each machine has its machine id. The machine comment will be on the machine table.

The table where there is a sequence of "lines" is a mainframe table. The mainframe screen is limited by 79 chars per line and to allow a comment with more than 79 chars the mainframe machine comment table was created.

The mainframe application is going away being replaced by a .NET web application.

Thanks!

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 5

Ah... got it. Seems like the right thing to do in this case.

Gus' code suggestion should work just fine except when you run into things like "&" and a few others. What are some of the spurious characters you're getting?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MissyDaisy Ten Centuries Points: 1164 More actions · Answer 6

Jeff,

I don't know how to get an ascii dump of a string from a particular column so I can't accurately say what characters they are.

Visually, it has spacing wider than a single white space.

Strange because G's sample only specifies the column value + a single white space.

Anyway, I ended up creating a function that makes use of coalesce.

Many thanks to everyone!

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 7

MissyDaisy (6/16/2009)
Jeff,
I don't know how to get an ascii dump of a string from a particular column so I can't accurately say what characters they are.
Visually, it has spacing wider than a single white space.
Strange because G's sample only specifies the column value + a single white space.
Anyway, I ended up creating a function that makes use of coalesce.
Many thanks to everyone!

Change the COALESCE to ISNULL... ISNULL is a fair bit faster than COALESCE on wider concatenations like this. It does take a lot of data to actually notice but in the face of potential scalability, I pull out all the stops. If someone tries to worry you about ANSI and "portable code", don't worry... the myth of truly portable code is, well, a myth. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 8

The wider spaces are probably from emptry strings. That would result in a double-space.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon