May 2, 2011 at 2:22 pm
I have a table that stores notes for a company. These notes are stored in a separate row each time they are entered in.
Company, Notes, TimeStamp
ABC Co, 'These are notes for today', '2011-05-01'
XYZ Co, 'Notes for XYZ Co', '2011-05-01'
ABC Co, 'Another set of notes', '2011-05-02'
What I'd like to do is concatenate the notes for each Company so I can have all the notes for that particular company in one field like so:
Company, All_Notes
ABC co, 'These are notes for today - Another Set of Notes'
XYZ Co, 'Notes for XYZ Co'
I know how to accomplish this using a cursor or temp table but is it possible with just a select statement?
TIA
May 2, 2011 at 2:33 pm
Is the note storage to remain unchanged? In other words, will you be leaving each note in its own record?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 2, 2011 at 2:38 pm
Yeah for now it is. I'm not so much concerned with the current way it's being stored but just extracting the data so we can import it into another system.
May 2, 2011 at 3:10 pm
This should help you out. This will show you a script on how to do it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 6, 2011 at 10:11 am
AVB, if you search this site for concatanation functions, you will find countless examples of basically the same thing as shown below. Please note, I have not tried the previous responses, and am not intending to imply there is anything wrong with them, this is just another way. I do the exact same thing you are needing to do using a function that includes a CHAR(10) character, so when you link a spreadsheet to it, the notes are separated by the line feed, with the date preceeding the note, ordered by most recent at the top of the cell. I don't know what you are doing with it, but you might find it useful someday if you haven't already. You will just have to tweak the table name, and maybe the date. One thing to note, when you pull a function like this into a query, it can have a huge impact on performance, so just make sure you are prudent with it's use.
CREATE FUNCTION [dbo].[fnCompanyNotes]
(
@Company VARCHAR(200)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Return VARCHAR(8000)
SELECT @Return = ISNULL(@Return + CHAR(10),'') + CONVERT(VARCHAR,[TimeStamp],101) +
' - ' + Notes
FROM TableName t
WHERE t.CompanyName = @company
ORDER BY [TimeStamp] DESC
RETURN @Return
END
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 6, 2011 at 11:01 am
Greg,
That's actually a great idea and an easy way to do it. I've done plenty of concatenation just like the select statement in your function but I just didn't think about putting it in a function. Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply