combine string from multiple records

  • 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.

  • 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

  • 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?

  • 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/

  • 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.

  • 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/

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

  • 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/

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

  • 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?

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

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