Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Mar 2006
    Posts
    82

    Unanswered: Is possible to concatenate a column?

    Hey all

    Just like the title asks...Is it possible...I have a table that stores memos but breaks the memo text field up and assigns a key to associate it all as one big text field in the application. What I am needing to do is to create a custom table for a customization to have the memo txt in one row per customer, per memo number. So, is there any way to concatenate memotext into one row per memo?

    Thanks
    tibor

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Sure you can! Although we need more info regarding your table construction and keys and the like.

    To concatinate the memo columns from different rows is easy, in the INSERT, you will just use the concatination operator (i.e., "...(MemoPart + MemoPart + MemoPart...) AS FullMemo...).

    The tricky part comes in building the select so that it pulls the memo parts based on the customer number and memo number association, and in the proper order/sequence (which, hopefully, your existing memoparts table provides a mechanism for). There is probably already a memopartnumber sequence construction there (maybe the key that "associates it all as one big text field in the application"?).

    We just need to see the DDL on the associated tables.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    OR

    You could use some SkullDugery in your Select Statement that will insert data into your New table (or even eliminate the need for a new table) - tehe

    Code:
    SELECT OuterTable.PKey , InnerTab.ConcatResultField
    FROM OuterTable
    LEFT JOIN(SELECT TabA.PKey,
    (SELECT Memo + Memo
    FROM OuterTable TabB
    WHERE TabB.PKey = TabA.PKey
    ORDER BY [Whatever]
    FOR XML PATH('') 
    ) ConcatResultField
    FROM OuterTable TabA
    GROUP BY PKey
    ) InnerTab ON OuterTable.PKey = InnerTab.PKey
    
    PS. your choice whether to use the FOR XML Path Trickery for the Order By - may not be supported in future versions

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by TallCowboy0614
    "...(MemoPart + MemoPart + MemoPart...) AS FullMemo...).
    when i do this with TEXT columns, i get the error message "Operand data type text is invalid for add operator."
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why isn't this a presentaion issue?

    Someone go find the blind dude
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You know, this really sounds more like a presentation issue.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    *rofl*!

    Oh and I agree with the blind dude.
    George
    Home | Blog

  8. #8
    Join Date
    Mar 2006
    Posts
    82
    Quote Originally Posted by TallCowboy0614
    Sure you can! Although we need more info regarding your table construction and keys and the like.

    To concatinate the memo columns from different rows is easy, in the INSERT, you will just use the concatination operator (i.e., "...(MemoPart + MemoPart + MemoPart...) AS FullMemo...).

    The tricky part comes in building the select so that it pulls the memo parts based on the customer number and memo number association, and in the proper order/sequence (which, hopefully, your existing memoparts table provides a mechanism for). There is probably already a memopartnumber sequence construction there (maybe the key that "associates it all as one big text field in the application"?).

    We just need to see the DDL on the associated tables.

    Sorry for the delay...

    Yes there is a SeqNo field for each memo and a memo number but the problem is there is no key but there is a customer number to play with instead.

    thanks for the reply, I just wanted to know if it was even possible and now the problem of creating that SELECT statement ensues, lol.

    Thanks
    tibor

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    You know, this really sounds more like a presentation issue.

    thank you

    this mess is too shot
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    deleted for duplicate post (more or less...)
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  11. #11
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by r937
    when i do this with TEXT columns, i get the error message "Operand data type text is invalid for add operator."
    Text? What is "text"?? I pretty much always use char() or varchar() and it works peachy for those types.

    Text data types are for use by those losers that need well...ummm..."text" data types

    Besides, this is really a presentation issue anyway.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by TallCowboy0614
    Text? What is "text"??
    oh, i dunno, it's just something i saw in post #1 --

    ... "the memo text field"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Mar 2006
    Posts
    82
    Ok, so I am confused. What exactly is a "presentation issue"?

  14. #14
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by r937
    oh, i dunno, it's just something i saw in post #1 --

    ... "the memo text field"
    Oh for crissake Rudy, you'll find this whole "guru" thing much easier if you don't allow yourself to become involved in the muddled world of technical accuracy and/or functionality details.

    It's much more valuable to the newbie to be given only tangentally-accurate information so that he/she can learn by the application of technically flawed advice. This method allow the inquiring person to learn by experience.

    So much to teach, so little time
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    LOL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •