Concatenation

Hello all,
I'm trying to combine two columns of data into a third column using a formula on the thrid column. Each of the columns could contain nulls and each of the columns could contain padding after or before the data. I'm trying to use the following formula yet SQL is throwing an error. Can someone provide another set of eyes to check this out?
ISNULL(LTRIM(RTRIM([user_Define_4a])),'') + ISNULL(LTRIM(RTRIM([user_Define_1])),'')
Thanksyou need to do the ISNULL before then TRIM. this is because a null value cannot be trimmed and should be converted to an empty string first. try:
LTRIM(RTRIM(ISNULL([user_Define_4a],''))) + LTRIM(RTRIM(ISNULL([user_Define_1],'')))|||Thank you. It works like a champ.

Concatenating fields over multiple rows

I have a query that produces seperate rows for people, but I want to combine them into one place. I tried doing this in SQL but apparently it's not very easy in SQL Server. You need to loop through a table using cursors. I'm not quite that advanced with SQL Server and was wondering if there might be an easier way just using SSRS.

In other words I have a table as such:

1 John Smith
2 Jane Doe
3 Matthew Jones

And I'd like to create one textbox that contains the following:

"John Smith, Jane Doe, Matthew Jones"

I've been drawing a blank. Anyone have any ideas?

Levi

Well, I found the following in the comments of this article:

http://www.sqlteam.com/article/converting-multiple-rows-into-a-csv-string-set-based-method

Code Snippet

declare @.list varchar(8000)

select @.list = Isnull(@.list,'') +';'+ theColumnToBeConcatenated

from yourtable

where yoursearchparameter='something'

select @.list

and tested it and found it worked quite well. For your example table, assuming the table name was NameTable and the field containing the names was FullName, try the following:

Code Snippet

DECLARE @.list VARCHAR(8000)

SELECT @.list = ISNULL(@.list, '') + ', ' + FullName

FROM NameTable

SELECT @.list

Good luck!

Larry

|||Larry, thanks. I actually found that same link but when I tried it I got an error. In the mean time the issue has become irrelevant as the database is changing, but I will note this for the future if a similar need arises and I need to try something like this again.

Levi
|||

yuou can't merge rows in SSRS for some strange reason.

what you could do in the sql is create an array, then do an array to text conversion.

|||

The code in the example above worked for me against the sample table created in the original article. I tried it on 32 bit SQL 2005 directly, in a report preview, and in a deployed report with no problems.

I can post the table creation code and actual query if it would help.

Larry