Post reply

Concatenate values from multiple records into single field

  • June 5, 2009 at 3:49 pm

    #210673

    Hi,

    I'm trying to transform some data that is broken out into multiple records into a single record. I've looked into looping the records and adding them into a variable but I can't seem to get the results I'm looking for.

    This is the basic table structure

    ID storyID ParagraphID Title Text

    20090605 1 1 Section Header Title1 paragraph1

    20090605 1 2 Section Header Title1 paragraph2

    20090605 1 3 Section Header Title1 paragraph3

    20090605 2 1 Section Header Title2 paragraph1

    20090605 2 2 Section Header Title2 paragraph2

    20090604 1 1 Section Header Title1 paragraph1

    20090604 1 2 Section Header Title1 paragraph2

    20090604 1 3 Section Header Title1 paragraph3

    What I would like to end up with is something similar to what is below in the order above.

    ID Text

    20090605 Section header+Paragraph+Paragraph+section+Paragraph+Paragraph...

    20090604 Section header+Paragraph+Paragraph+section+Paragraph+Paragraph...

    Any help would be greatly appreciated! Thanks!

  • June 5, 2009 at 4:16 pm

    #1005576

    Hi,

    would you please be a little more specific on what you're trying to achieve?

    I don't know how your expected result "20090605 Section header+Paragraph+Paragraph+section+Paragraph+Paragraph..." would correlate in any way with your sample data...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • June 5, 2009 at 4:39 pm

    #1005580

    Sorry...I'll try to clarify....

    This is for a type of "article" page for lack of a better term. Each record has the section header, a paragraph of text, and an order field. What I want to do is concatenate the contents from each record into a single field.

    I found an example using For XML Raw but it's not exactly what I need but it's close however I'm not sure how to update it for my purposes.

    So I want to turn this...

    ArticleID Title Text

    100 Section1 "Text for paragraph1"

    100 Section1 "Text for paragraph2"

    100 Section1 "Text for paragraph3"

    100 Section2 "Text for paragraph1"

    101 Section1 "Text for paragraph1"

    101 Section1 "Text for paragraph2"

    101 Section1 "Text for paragraph3"

    101 Section2 "Text for paragraph1"

    into this:

    ArticleID Text

    100 "Section1Text for Paragraph1 Text for Paragraph2 Text for Paragraph3 ...Section2..."

    101 "Section1Text for Paragraph1 Text for Paragraph2 Text for Paragraph3 ...Section2..."

  • June 5, 2009 at 5:11 pm

    #1005588

    SELECT

    ArticleID,

    STUFF((SELECT ' ' + Title + ' ' +Text FROM TABLE t2 WHERE t2.ArticleID = t1.ArticleID FOR XML PATH('')),1,1,'')

    FROM

    TABLE t1

    GROUP BY

    ArticleID


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • June 8, 2009 at 5:28 pm

    #1006357

    Hi, thanks for your reply. I'll try this.

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

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