Solved

Aggregating strings in SQL

Posted on 2008-10-10
17
251 Views
Last Modified: 2012-05-05
Hi,

I have a table containing client visit records that looks like the following (I've simplified it for illustration)

Visits:

ClientID_FK  VisitDate        VisitNotes
1                  29/05/08      'Note 1.'
2                  28/05/08      'Called by Phone.'
3                  03/05/07      'Will call back.'
3                  14/09/08      'Note 2.'
2                  13/08/08      'Refer to notes.'
2                  04/04/08      'blah blah blah.'
1                  11/11/06      'More notes...'

There is also a clients table which is unique on the client number, it has a VarChar(MAX) comments field which I'd like to be a concatenation of the visit notes in reverse chronological order... it needs to end up looking something like this..

Clients:

ClientID  Comments
1            'Note 1. More notes...'
2            'Refer to notes. Called by Phone. blah blah blah.'
3            'Note 2. Will call back.'

I've tried grouping the visit table by client number but don't know what aggregate function to use to get what I need...assuming that's the best approach.

Any ideas folks?


Thanks loads
Iain
0
Comment
Question by:iaing1000
  • 5
  • 3
  • 3
  • +4
17 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22689493
please check out this syntax:
http://anthony-yio.blogspot.com/2007/12/mssql-groupconcat.html
using xml and cross apply in sql 2005...
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22689521
you can do this if you're using 2005:


create table #t(clientid tinyint, visitdate varchar(10), visitnotes varchar(100))

insert into #t
select 1    ,              '29/05/08'      ,'Note 1.' union all
select 2    ,              '28/05/08'     ,'Called by Phone.' union all
select 3    ,              '03/05/07'    ,  'Will call back.' union all
select 3    ,              '14/09/08'   ,   'Note 2.' union all
select 2    ,              '13/08/08'  ,    'Refer to notes.' union all
select 2    ,              '04/04/08' ,     'blah blah blah.' union all
select 1    ,              '11/11/06',      'More notes...'

select distinct o.clientid,
(
select visitnotes + ' '
from #t i
where i.clientid = o.clientid
order by visitdate desc
for xml path('')
)

from #t o
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22689526
Ok.  this is my first time attempting to use cross apply, so if it doesn't work the first time, be patient while I fix it and report any errors.


This SQL:
select c.clientID,c1.comments from Clientsc
cross apply dbo.fn_ClientComments(c.clientID)c1

Needs this function:


create function fn_ClientComments (@clientID int)

returns @ClientComments TABLE (ClientID int, comments (max))

as

begin

declare @Comments nvarchar(max)

select @comments = isnull(@comments + '; ')+Comments from Visits

where clientID = @clientID

insert into @ClientComments values(@ClientID,@Comments)

end

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22689548
No need for cross apply here.  ;)
0
 
LVL 10

Expert Comment

by:c0ldfyr3
ID: 22689603
Give this a go.. If they come out backwards change the order by. Also change the declarations to match the fields I've just used INT as an example...

DECLARE @ClientID_FK	AS INT

DECLARE @VisitNotes	AS VARCHAR(MAX)

DECLARE @COMMENT	AS VARCHAR(MAX)

DEClARE @LAST_CLIENT	AS INT
 

SET @LAST_CLIENT = -1
 

DECLARE csrCursor CURSOR FOR

	SELECT ClientID_FK, VisitNotes FROM [ClientNotes] Order BY ClientID_FK ASC, VisitDate DESC

	FROM rule_rp_report
 

OPEN csrCursor
 

FETCH NEXT FROM csrCursor INTO @ClientID_FK, @VisitNotes
 

WHILE @@FETCH_STATUS = 0

BEGIN

	IF ( @LAST_CLIENT <> @ClientID_FK ) -- When client id changes update the last one.

	BEGIN

		IF ( LEN(@COMMENT) > 0 )

		BEGIN

			SET @COMMENT = SUBSTRING(@COMMENT, 1, LEN(@COMMENT)-1) -- Remove trailing ,

			UPDATE [ClientTable] SET comments = @COMMENT WHERE ClientID = @LAST_CLIENT -- Update the client table.

		END

		SET @COMMENT = '' -- Reset comment variable

		SET @LAST_CLIENT = @ClientID_FK -- Update last client

	END

	

	SET @COMMENT = @COMMENT + @VisitNotes + ',' -- Add next note.
 

	FETCH NEXT FROM csrCursor INTO @ClientID_FK, @VisitNotes

END
 

IF ( LEN(@COMMENT) > 0 ) -- Commit the last update.

BEGIN

	SET @COMMENT = SUBSTRING(@COMMENT, 1, LEN(@COMMENT)-1) -- Remove trailing ,

	UPDATE [ClientTable] SET comments = @COMMENT WHERE ClientID = @LAST_CLIENT -- Update the client table.

END

		

CLOSE csrCursor

DEALLOCATE csrCursor

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22689610
...and you definitely do not need to use a cursor.  :)
0
 
LVL 10

Accepted Solution

by:
c0ldfyr3 earned 500 total points
ID: 22689618
Woops, remove the 'FROM rule_rp_report' in that hehe.

DECLARE @ClientID_FK	AS INT

DECLARE @VisitNotes	AS VARCHAR(MAX)

DECLARE @COMMENT	AS VARCHAR(MAX)

DEClARE @LAST_CLIENT	AS INT
 

SET @LAST_CLIENT = -1
 

DECLARE csrCursor CURSOR FOR

	SELECT ClientID_FK, VisitNotes FROM [ClientNotes] Order BY ClientID_FK ASC, VisitDate DESC
 

OPEN csrCursor
 

FETCH NEXT FROM csrCursor INTO @ClientID_FK, @VisitNotes
 

WHILE @@FETCH_STATUS = 0

BEGIN

	IF ( @LAST_CLIENT <> @ClientID_FK ) -- When client id changes update the last one.

	BEGIN

		IF ( LEN(@COMMENT) > 0 )

		BEGIN

			SET @COMMENT = SUBSTRING(@COMMENT, 1, LEN(@COMMENT)-1) -- Remove trailing ,

			UPDATE [ClientTable] SET comments = @COMMENT WHERE ClientID = @LAST_CLIENT -- Update the client table.

		END

		SET @COMMENT = '' -- Reset comment variable

		SET @LAST_CLIENT = @ClientID_FK -- Update last client

	END

	

	SET @COMMENT = @COMMENT + @VisitNotes + ',' -- Add next note.
 

	FETCH NEXT FROM csrCursor INTO @ClientID_FK, @VisitNotes

END
 

IF ( LEN(@COMMENT) > 0 ) -- Commit the last update.

BEGIN

	SET @COMMENT = SUBSTRING(@COMMENT, 1, LEN(@COMMENT)-1) -- Remove trailing ,

	UPDATE [ClientTable] SET comments = @COMMENT WHERE ClientID = @LAST_CLIENT -- Update the client table.

END

		

CLOSE csrCursor

DEALLOCATE csrCursor

Open in new window

0
 
LVL 10

Expert Comment

by:c0ldfyr3
ID: 22689622
Do not 'need'? It's provided to be used....
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22689646
Actually, for sorting my function would need to be:

create function fn_ClientComments (@clientID int)
returns @ClientComments TABLE (ClientID int, comments (max))
as
begin
declare @Comments nvarchar(max)
select @comments = isnull(@comments + '; ')+Comments from Visits
where clientID = @clientID order by visitdate desc
insert into @ClientComments values(@ClientID,@Comments)
end
0
 

Author Comment

by:iaing1000
ID: 22689680
Hi Folks,

Thanks for the fast responses.

Just an incredibly brief look suggests that I can't use the first one with the long INSERT since there are 94000 in the visit table and 34000 in the client table (please understand if I've misunderstood!). I'll have a look tomorrow as I need to leave now,

Thanks again
Iain  
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22690267
What would make you think you couldn't use one just because there are 94000 & 34000 records?

grr.... datatype dummie :)


create function fn_ClientComments (@clientID int)

returns @ClientComments TABLE (ClientID int, comments varchar(max))

as

begin

declare @Comments nvarchar(max)

select @comments = isnull(@comments + '; ')+Comments from Visits

where clientID = @clientID order by visitdate desc

insert into @ClientComments values(@ClientID,@Comments)

end

Open in new window

0
 
LVL 2

Expert Comment

by:jjerome00
ID: 22690745
All these suggestions deal with looping through the results to get the aggregated string, but I'm a little puzzled as to why you want to do this for every record in your Clients table.  I can't imagine anyone looking at all 34,000 records at the same time, which would lead me to guess that you want to use this for a grid in an application and you want to use a simple query to load it?  

I might suggest adding another table that keeps a "running aggregate" (or intersection) of both tables, named "ClientVists" or similar.  You could attach a trigger to the Visits table that would update the ClientVisits table anytime a new Visit has been logged.  Assuming someone is depending on this aggregated data in a list, a separate table could give you good performance for the end user.

I hope I'm making sense.   I've also pasted my favorite method of looping through data below, hopefully I got the syntax right using your tables.  Good luck.









declare @id as int;

declare @finalSTR varchar(max);

declare @tmpSTR varchar(max);
 

--gather data into temp table

select 

	row_number() over (order by VisitDate) as id, 

	VisitNotes

into #tmpTBL

from visits

where clientID = 1;
 

set @finalSTR = '';
 

-- loop through temp table, building string

while exists (select top 1 * from #tmpTBL)

begin

	select top 1 @id=id, @tmpSTR=VisitNotes from #tmpTBL;
 

	select @finalSTR = @finalSTR + @tmpSTR;
 

	delete #tmpTBL where id = @id;

end 
 

select @finalSTR;
 

drop table #tmpTBL;

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22690883
http:#22690745

It is not necessary to create a loop like that.  The methods above (http:#22689493, http:#22689521 and http:#22689646) will be much faster than loading into a temp table and building a string in a loop like this.
0
 
LVL 10

Expert Comment

by:3_S
ID: 22690959
Maybe a simple join would work
Join your client table with the visit table (only records which are in both tables will show up)

select visitnotes,clientid,visitdate from visits join clients on clientid_FK=clientid group by clientid,visitdate desc

Remove the desc If you want to see the first visit at place one instead of the last one.
0
 

Author Comment

by:iaing1000
ID: 22695679
Hi Folks,

Just so you know, this is required because I'm trying to migrate a legacy database. The old Visits table stores everything, there is no Clients table in the old db! This effectively means nothing is normalised and that I have to spend time trying to extract the client data in this way so that I can put it in the new system's Clients table. Hence why getting the notes in this way...

Brandon: I'm struggling with your code a little bit, although it looks like the best shot. Specifically, can you be more verbose in your cross apply select, using the precise table/field names explicitly as in the example I gave...I must admit I've never used cross apply and am no DB expert so expect to have to walk me through this real slow! Also, when I try to execute the function from the SQL Server 2005 Management Studio it complains that 'The last statement included within a function must be a return statement.' ....is executing it what I'm even supposed to be doing with the function before running the Select? And I may be wrong, but in the function you have the clause '+ Comments FROM Visits', yet there is no Comments field in the Visits table...did you mean the VisitNotes? That said, I can see the logic in it and it seems promising.

It might be worth mentioning that I don't need a repreatable process, just something that'll get me over this immediate hurdle, I was expecting something quick from the command line such as: Select SUM(VisitNotes),...blah blah blah...FROM VisitNotes GROUP BY ClientID_FK. Of course, this doesn't work but you can see my thinking....oh well.


Thanks again.
Iain

0
 

Author Comment

by:iaing1000
ID: 22695893
Hi C0ldfyr3,

Actually your solution worked perfectly first time around. Very accurate.

I noted that if any of the VisitNotes were NULL then it rendered the whole string for that client NULL, but I got around it by just setting NULL values to '' empty strings.


Thanks loads
Iain
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22699200
Wow... A cursor?  Well at least you are only doing it for a migration.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now