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


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|||"...(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."|||Why isn't this a presentaion issue?

Someone go find the blind dude|||You know, this really sounds more like a presentation issue.|||*rofl*!

Oh and I agree with the blind dude.|||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|||You know, this really sounds more like a presentation issue.

thank you

this mess is too shot|||deleted for duplicate post (more or less...)|||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.|||Text? What is "text"?? oh, i dunno, it's just something i saw in post #1 --

... "the memo text field"|||Ok, so I am confused. What exactly is a "presentation issue"?|||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 ;)|||LOL :cool:|||A presentation issue is an issue regarding how the data is displayed to the user, rather than having to do with the content of the data itself. SQL should not be used to handle presentation issues for several reasons. First, the SQL language was not designed with the intention of formatting data, but is optimized for retrieving and filtering data. Second, the database server should deliver the data in a generic format allowing different applications to display the same dataset in different ways. Third...
...ummm...third...
...thirdly, never talk about Fight Club.|||Ah, ok.

Well it seemed like a valid question as the application itself gives very very little leeway as to what can be done through its customizer unless you tap into the source code which we can not do. Thanks for the help.|||Ah, ok.

Well it seemed like a valid question as the application itself gives very very little leeway as to what can be done through its customizer unless you tap into the source code which we can not do. Thanks for the help.

oh for the love of...

How many "4GL" pieces of garbage are actually out there...

silver bullet my a$$|||if you have nothing nice to say, keep it to yourself...it is that simple. i come here for help, not criticism.|||what are you talking about?

Did you write the app? Sounds like a 3rd party off the shelf application that you are stuck with or did you write, in which case why don't you open up the code.

And if you did not write it, then you shouldn't touch the code because I'm sure it will invalidate any support aggreement

What was that line in the first Lethal weapon when Riggs first meets Roger?|||if you have nothing nice to say, keep it to yourself...it is that simple. i come here for help, not criticism.I don't think Brett was aiming the criticism at you, but rather railing at the plethora, or veritible cornucopia, as it were, of crap applications out there that were initially sold as do-all, cure-all bits-o-bytes.

FYI, seldom on this or any internet bulletin board does saying something like "if you have nothing nice to say, keep it to yourself..." result in that direction being taken.|||ok, i do apologize but it just seemed directed towards me. the 3rd party app is an erp packack and we are upgrading from one tier to the next and the table structures are totally different. when the client says they need this mod, well you cant just say "no its not possible" nor can you just hop into the source so you have to make do with what ya got. in this case, we have to create a custom table to pull the info from the former erp and how it had the info stored.

again, i do apologize.|||Well why don't you post the ddl of the old and new tables and what you have to map|||Ok this is the function that I have now in order to do the deeds:

CREATE FUNCTION dbo.GetMemo(@.MemoID Varchar(15))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SELECT @.Output = COALESCE(@.Output + '', '') + X.MemoText
FROM dbo.ARP_CustomerMemoHeader X

WHERE X.MemoNumber = @.MemoID
ORDER BY X.SeqNumber

RETURN @.Output
END
GO

And when I run this:

SELECT Distinct dbo.GetMemo('0221AD')
FROM ARP_CustomerMemoHeader
Where MemoNumber = '0221AD'

It gives me the results needed.

Now there is the matter of creating an update statement for the custom table that has everything but the memotext field populated and get this into that table.

thanks for the help and I owe a beer to those I got short with.:beer:|||thanks for the help and I owe a beer to those I got short with.:beer:Isn't it amazing that we are such a diverse and widely sprinkled lot, and yet we all really speak the same language where it is most important to do so ;)|||thanks for the help and I owe a beer to those I got short with.:beer:I found your comments deeply offensive. About 12 oz deep, actually.|||tallboy for me please!|||I found your comments deeply offensive. About 12 oz deep, actually.

I thought he was refering to about 2 inches.....and that's what yo uwere offended about

Lion King: Ah yes, the truth hurts|||Tibor: We need DDL|||tallboy for me please!
do what?

Oh, you are talking about glass size...scared me for a minute. ;)