Concatenating text field to one row but results missing data

[makeitwork09]

I want one column and one row that acts as a concatenated list, separated with a carriage return.

I have a table with the fields code, loancomment, commentdate, and note1.

Note1 is a datatype of text.

The data in the table, if I select the note1 records returns 3 rows of data. I want those three rows to be one row with all three rows in that one row.

Therefore, a straight query for note1 has a result set of:
(1) $30,525,000 mortgage loan with an initial funding of $28,925,000 and a commitment to fund up to $1,600,000 for tenant improvement costs and leasing commissions.
(1) $30,525,000 mortgage loan with an initial funding of $28,925,000 and a commitment to fund up to $1,600,000 for leasing commissions.
(2) 21,775,000 mortgage loan with a commitment to fund up to $2,000,000 for tenant improvements.
3 row(s) affected

I want to have
(1) $30,525,000 mortgage loan with an initial funding of $28,925,000 and a commitment to fund up to $1,600,000 for tenant improvement costs and leasing commissions.
(1) $30,525,000 mortgage loan with an initial funding of $28,925,000 and a commitment to fund up to $1,600,000 for leasing commissions.
(2) 21,775,000 mortgage loan with a commitment to fund up to $2,000,000 for tenant improvements.
1 row(s) affected

I have all of the following, but the last row is not returning at all the text and only part of the second note is returning.

select
(
select cast(om.note1 as varchar(max)) + char(10)
from loancomm as om
where om.commtopic = 'ic_footnot'
for xml path (''),type
)

select
(
stuff((select cast(om.note1 as varchar(max)) + char(10)
from loancomm as om
where om.commtopic = 'ic_footnot'
for xml path (''),type).value('.', 'varchar(max)'),1,0,'')
)

select
(
select cast(om.note1 as varchar(max)) + char(10)
from loancomm as om
where om.commtopic = 'ic_footnot'
for xml path (''),type).value('substring((./text())[1], 1)', 'varchar(max)')

The results I am getting for the one row using all of the above is:
(1) $30,525,000 mortgage loan with an initial funding of $28,925,000 and a commitment to fund up to $1,600,000 for tenant improvement costs and leasing commissions.
(1) $30,525,000 mortgage loan with an initial funding of $28,925,000 and a commitment to fu

(1 row(s) affected)

 

[gmmastros]

It's possible that your functionality is working correctly, but that SQL Server Management Studio is not showing it to you correctly.

Try this...

In SQL server Management Studio, click Tools -> Options
Expand "Query Results"
Expand "SQL Server"
Click on "Results To Grid"

What value are you showing for "Non XML data"? I'm guessing it's 256. This configuration option truncates your data to 256 characters prior to showing it to you within SQL Server Management Studio. If you change this value to something larger, and re-run your query, does it now work properly.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[makeitwork09]

Thanks for the reply George.

Non XML Data is showing as 65535
XML data is 2 MB

 

[gmmastros]

Are you showing results as text instead?

Try this...

In SQL server Management Studio, click Tools -> Options
Expand "Query Results"
Expand "SQL Server"
Click on "Results To Text"

Take a look at the setting for "Maximum number of characters displayed in each column"



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[makeitwork09]

Thanks George - that was it.

 

[gmmastros]

Aren't you glad it was a silly configuration option in SSMS instead of a flaw with your code?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[makeitwork09]

OMG - yes I am George. I am indeed. Thanks again for pointing that out.