Multiple Records into one

Question

Multiple Records into one

terry.home

Ten Centuries

Points: 1004
More actions
January 14, 2016 at 2:03 pm

#97471

I seem to have forgotten how to work in SQL2000 and have been asked to extract data from this so would appreciate some help.
Trying to work out how to join data from different records into one record and seperate the data with a line break.
Here is a sample of TBL_Notes data
TID NOTE_ID DATE NOTES
--- -------- ----------- ------------------------
1 12585 21/02/2003 Not all details provided
2 12585 22/02/2003 Letter sent to applicant
3 12636 05/03/2003 Application Received
4 12585 06/03/2003 No response from applicant
5 12636 07/03/2003 Application approved
6 12434 15/04/2003 Application Received
7 12585 17/04/2003 Second letter sent to applicant
8 12585 29/04/2003 Cancelled due to lack of response
I would like a query to return DISTINCT values from NOTE_ID and CONCATENATE all the results from NOTES column onto the same row but seperating the values with a line break.
Result I hope to see
NOTE_ID NOTES
--------- -------------------------------------------
12585. . . 21/02/2003 Not all details provided . . . . . . . . . .(Ignore the dots (added for allignment)
. . . . . . . 22/02/2003 Letter sent to applicant
. . . . . . . 06/03/2003 No response from applicant
. . . . . . . 17/04/2003 Second letter sent to applicant
. . . . . . . 29/04/2003 Cancelled due to lack of response
--------- -------------------------------------------
12636. . . 05/03/2003 Application Received
. . . . . . . 07/03/2003 Application approved
--------- -------------------------------------------
12434. . . 15/04/2003 Application Received
--------- -------------------------------------------
I realise that the results will not display on a new line in SSMS but would like to put the linebreak in the T-SQL statement so that the results hopefully display correctly on the final presentation.
PS. I have tried XML but unfortunately this does not seem to work on SQL2000
Any help will be greatly appreciated
Thanks in advance

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

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Considering that you're working with SQL 2000, an option could be using a scalar function. I'm not sure that the order will always work, but it's an option.

CREATE TABLE tblNOTES(

TID int,

NOTE_ID int,

DATE datetime,

NOTES varchar(8000)

);

INSERT INTO tblNOTES

SELECT 1, 12585, '20030221', 'Not all details provided ' UNION ALL

SELECT 2, 12585, '20030222', 'Letter sent to applicant ' UNION ALL

SELECT 3, 12636, '20030305', 'Application Received ' UNION ALL

SELECT 4, 12585, '20030306', 'No response from applicant ' UNION ALL

SELECT 5, 12636, '20030307', 'Application approved ' UNION ALL

SELECT 6, 12434, '20030415', 'Application Received ' UNION ALL

SELECT 7, 12585, '20030417', 'Second letter sent to applicant ' UNION ALL

SELECT 8, 12585, '20030429', 'Cancelled due to lack of response' ;

GO

CREATE FUNCTION dbo.All_Notes(

@NOTE_ID int

)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @Notes varchar(8000);

SET @Notes = '';

SELECT @Notes = @Notes + CONVERT(char(11), DATE, 103) + NOTES + CHAR(10)

FROM tblNOTES

WHERE NOTE_ID = @NOTE_ID

ORDER BY TID;

RETURN @Notes;

END

GO

SELECT DISTINCT NOTE_ID, dbo.All_Notes(NOTE_ID)

FROM tblNOTES

ORDER BY NOTE_ID

GO

DROP TABLE tblNOTES;

DROP FUNCTION All_Notes;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

terry.home Ten Centuries Points: 1004 More actions · Answer 2

Thank you very much for your very quick response. Much appreciated. Tried it today at work and just made a few minor modifications but essentially it worked a treat.

Also makes it much more effective using a function so I have left the function on the server.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

Luis Cazares (1/14/2016)
SELECT @Notes = @Notes + CONVERT(char(11), DATE, 103) + NOTES + CHAR(10) FROM tblNOTES WHERE NOTE_ID = @NOTE_ID ORDER BY TID;

Note that the technique, as shown with an ORDER BY referencing a column not used in the SELECT-column list, can give unpredictable results. i.e. do not use it!

Credit to Erland for pointing this out to me a while back. Here is a connect item on it and some more info. Oddly, the KB usually referenced, KB287515, is not currently available.

https://connect.microsoft.com/SQLServer/feedback/details/383641/in-variable-concatenation-and-order-by-filters-results-like-where-condition

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Ed Wagner SSC Guru Points: 287024 More actions · Answer 4

Orlando Colamatteo (1/16/2016)
Luis Cazares (1/14/2016)
SELECT @Notes = @Notes + CONVERT(char(11), DATE, 103) + NOTES + CHAR(10) FROM tblNOTES WHERE NOTE_ID = @NOTE_ID ORDER BY TID;
Note that the technique, as shown with an ORDER BY referencing a column not used in the SELECT-column list, can give unpredictable results. i.e. do not use it!
Credit to Erland for pointing this out to me a while back. Here is a connect item on it and some more info. Oddly, the KB usually referenced, KB287515, is not currently available.
https://connect.microsoft.com/SQLServer/feedback/details/383641/in-variable-concatenation-and-order-by-filters-results-like-where-condition

Now this is an interesting one. I read the connect item and it's closed, marked as being ~~something we didn't take into account for for some strange and unknown reason, but we'll never admit it~~ by design. I see that MS says the only "guaranteed mechanisms" are cursor (yuck), for xml (I use it frequently, but not in SQL 2000) and CLR (not necessary and won't support the order by anyway). It figures.

I've never seen the technique produce inaccurate results. Can you provide an example of where it screws up? I'm incredibly curious.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 5

I'll dig/work one up and post back.

The XML technique is OK, just, OK. And if used I prefer to add TYPE to avoid entitization.

For CLR http://groupconcat.codeplex.com will get you going.

Aaron Bertrand did a formal compare of all the group concat methods with performance analysis too that ahows them all.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

Ed Wagner (1/16/2016)
I've never seen the technique produce inaccurate results. Can you provide an example of where it screws up? I'm incredibly curious.

I've seen it happen once when creating a dynamic pivot. The dates were completely unsorted. The only problem is that I didn't dig much into the issue and just changed to the FOR XML method. Now I'm not able to reproduce it, but I could try and post back if Orlando doesn't do it first.

EDIT:

Here are links to Aaron's articles:

http://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation

http://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation-2

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/03/08/t-sql-tuesday-16-this-is-not-the-aggregate-you-re-looking-for.aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 7

There is a repro and explanation is in the SSC Forum thread linked to in the Connect item albeit using an expression in the ORDER BY:

http://www.sqlservercentral.com/Forums/Topic607455-145-1.aspx

Here is another repro using a column from the table but again, as an expression:

http://blog.sqlauthority.com/2009/09/20/sql-server-execution-plan-and-results-of-aggregate-concatenation-queries-depend-upon-expression-location/[/url]

Here is the guidance from Erland. The "article" being referenced is KB287515 which, inexplicably, is no longer available on Microsoft's site.

http://www.sqlservercentral.com/Forums/FindPost1477372.aspx

One could argue that the warnings in the article do not apply to every use of the variable-based grouped-concatenation technique, e.g. the article may not specifically apply to what Luis offered. I myself have argued for the use of the variable-based technique because I like the tightness of the syntax over the XML methods (not to mention the CPU and entitization baggage the XML methods bring) plus I have not witnessed problems in how I have used the technique. In the end I changed my mind because I think Erland is right and the technique should be avoided where possible.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Ed Wagner SSC Guru Points: 287024 More actions · Answer 8

Thanks, Luis and Orlando, for the examples. I used FOR XML and also avoid entities by using TYPE. I also do concatenation into a variable where appropriate and I've never run into a problem. I'll be playing with the examples when I get to work on Monday.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions