Concatenating column values?

Question

Post reply

Concatenating column values?

alo

Grasshopper

Points: 17
More actions
May 20, 2002 at 1:25 pm

#158245

Hi,
I'm trying to concatenate the values of a column over mulitple records into the column of a single record.
Example:
tbl1 (col areas)
1.monkey
2.tiger
3.lion
4.shark
tbl2 (col zoo)
1. monkey tiger lion shark
Any suggestions?

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

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

select a.cola + b.cola + c.cola

from tableA a

inner join tableA b

on a.something = b.something

...

Steve Jones

steve@dkranch.net

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Also keep in mind that if any one of the columns is null then the concatenated value will be null. If this will be possible and you want to avoid use ISNULL to handle.

Ex.

ISNULL(col1,'') + ISNULL(col2,'') + ISNULL(col3,'')

So if col2 is null then you get col1col3 and not NULL.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Eve SSCrazy Points: 2480 More actions · Answer 3

quote:
ISNULL(col1,'') + ISNULL(col2,'') + ISNULL(col3,'')
So if col2 is null then you get col1col3 and not NULL.

Also, if you want to return NULL if none of the values present in all three columns use the NULLIF function:

NULLIF(ISNULL(col1,'') + ISNULL(col2,'') + ISNULL(col3,''),'')

Vladimir Sim Ten Centuries Points: 1310 More actions · Answer 4

Hi,

I have similar problem in concentrating my column.

I have the following query

select comp_nm from xxx

where comp_nm_id = 10000

The result will be

comp_nmid, comp_nm

================

1000, how

1000,are

1000,you

If I want to concatenate it into a single string like ' how are you'

How can I do it.

Thank you

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

This is a very "old" problem and there must be a thousand or so posts on how to do this on this forum alone (search does work)... but, here it is, one more time...

DECLARE @Result VARCHAR(8000)

SELECT @Result = ISNULL(@Result+' ','') + comp_nm

FROM yourtable

WHERE comp_nm_id = 10000

SELECT @Result

Also, be advised that this is a pretty bad thing to do in a database in 99.9999% of all cases ...

--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)