combine string from multiple records

Question

Post reply

combine string from multiple records

skb 44459

SSCarpal Tunnel

Points: 4753
More actions
September 2, 2011 at 7:21 am

#243311

I know i can write

select sum(price) from orders

how do i combine text from multiple records

say my table notes has field note with following record

sam

robert

jim

matt

the output should be

sam robert jim matt

from the select statement. It will be better if i can add return carrage between records.

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

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

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

Look at PIVOT, or search for crosstab queries. That is what you are attempting here. If you know the number of rows it's easy. If not, it becomes a little more complicated.

PIVOT - http://msdn.microsoft.com/en-us/library/ms177410.aspx

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

R.P.Rozema SSChampion Points: 12305 More actions · Answer 2

Neither pivot nor cross-tab. Just a simple trick using for xml path(). Does any length of texts (up to varchar(max) - 1), and any number of rows.

declare @tbl table (



      txt varchar(max)

   );

insert @tbl( txt)

select 'this' union all

select 'is' union all

select 'a' union all

select 'test'

select stuff((

   select ' ' + t.txt as [text()]

   from @tbl t

   for xml path(''), type

).value('.','varchar(max)'), 1, 1, '')

I've put spaces in between the words, just ran out of "return carrage"s 😉 (but you may put char(13) instead of the single space string in the inner select statement to get what you probably intended...)

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Sean Lange SSC Guru Points: 286562 More actions · Answer 3

I too was guessing the OP wanted to look at cross tabs but since that wasn't a single field i knew it was close but not quite. This is a pretty slick piece of code. Added that little snippet to my vault. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

skb 44459 SSCarpal Tunnel Points: 4753 More actions · Answer 4

This works great

.

select stuff((

select ' ' + ReportNotesView.note as [text()]

from ReportNotesView WHERE RefRowPointer = '06F7539D-44B3-4438-BE89-6FB1F45B569E'

for xml path(''), type

).value('.','NTEXT'), 1, 1, '')

it gives error

Msg 9500, Level 16, State 1, Line 1

The data type 'NTEXT' used in the VALUE method is invalid.

my ReportNotesView.note is a ntext type.

Also i would like to add CHR(10) between each record.

Sean Lange SSC Guru Points: 286562 More actions · Answer 5

You should use nvarchar(max) instead of ntext. text and ntext are deprecated and their usage is discouraged. To put in your carriage return just replace the ' ' with char(13)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

The XML method won't work on NTEXT, period. Also be advised than NTEXT has been deprecated in favor of NVARCHAR(MAX). Since you're posting in a 2k8 forum, I assume you have 2k8 to do that with.

Also, if you want a CHAR(10) after each element, just add it to the code using + CHAR(10) as you would with any other concatenation.

And, to be sure, CHAR(10) is NOT a carriage return character. Char(10) is the "line feed" or "new line" character. Char(13) is the carriage return character.

Although I don't agree with the tone, I agree with Celko's thoughts in this matter... what are you doing this for and why?

--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)
Intro to Tally Tables and Functions

Sean Lange SSC Guru Points: 286562 More actions · Answer 7

Jeff Moden (9/2/2011)

The XML method won't work on NTEXT, period. Also be advised than NTEXT has been deprecated in favor of NVARCHAR(MAX). Since you're posting in a 2k8 forum, I assume you have 2k8 to do that with.

Also, if you want a CHAR(10) after each element, just add it to the code using + CHAR(10) as you would with any other concatenation.

And, to be sure, CHAR(10) is NOT a carriage return character. Char(10) is the "line feed" or "new line" character. Char(13) is the carriage return character.

Although I don't agree with the tone, I agree with Celko's thoughts in this matter... what are you doing this for and why?

Is there an echo in here? 😉

My guess is this is going to be for presentation. Basically took multiple rows, turned them into a single row and then added carriage returns for display. 😎

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Sean Lange (9/2/2011)

Jeff Moden (9/2/2011)

The XML method won't work on NTEXT, period. Also be advised than NTEXT has been deprecated in favor of NVARCHAR(MAX). Since you're posting in a 2k8 forum, I assume you have 2k8 to do that with.

Also, if you want a CHAR(10) after each element, just add it to the code using + CHAR(10) as you would with any other concatenation.

And, to be sure, CHAR(10) is NOT a carriage return character. Char(10) is the "line feed" or "new line" character. Char(13) is the carriage return character.

Although I don't agree with the tone, I agree with Celko's thoughts in this matter... what are you doing this for and why?

Is there an echo in here? 😉

My guess is this is going to be for presentation. Basically took multiple rows, turned them into a single row and then added carriage returns for display. 😎

Heh... apparently. I was typing a reply and got interrupted. I didn’t look to see if someone beat me to it when I finally hit the “submit” button. It does show concurrance, though. 😛

--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)
Intro to Tally Tables and Functions

diamondgm SSCrazy Points: 2824 More actions · Answer 9

Read Dr. Codd's rules; the Information Principle and First Normal Forms (1NF) say that each column of a row holds a scalar value that represents a fact. Sticking them into one column is a violation of 1NF.

Joe,

I don't have nearly the experience that you have, so please consider my comment as humble and enquiring.

I'm thinking out loud and not declaring fact and would really appreciate your response.

Aside from the semantics of rows/records & columns/fields...

A collection of scalar values can be aggregated and expressed as a higher state of abstraction and in this "new" form be considered scalar?

The values you are aggregating might in themselves be the result of prior aggregation.

In the case of numbers, I suppose there is little space for creativity. The AVG(columnA) = a set value; a scalar set value.

Though, when aggregating text values for whatever reason, probably cosmetic, what should one do?

If one had a table (Month, Turnover) and you wanted the SUM of Tunrover for Months Jan, Feb, Mar - what would the fault be in describing it as 'Jan,Feb,Mar',176216?

Surely you could describe it in any way that proves meaningful and the string 'Jan,Feb,Mar' could be considered scalar?

My feeling is that you're safe as long as you maintain clarity on your current level of abstraction?