Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

Aggregating strings in SQL

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
iaing1000
Asked:
iaing1000
  • 5
  • 3
  • 3
  • +4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please check out this syntax:
http://anthony-yio.blogspot.com/2007/12/mssql-groupconcat.html
using xml and cross apply in sql 2005...
0
 
chapmandewCommented:
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
 
BrandonGalderisiCommented:
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
chapmandewCommented:
No need for cross apply here.  ;)
0
 
c0ldfyr3Commented:
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
 
chapmandewCommented:
...and you definitely do not need to use a cursor.  :)
0
 
c0ldfyr3Commented:
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
 
c0ldfyr3Commented:
Do not 'need'? It's provided to be used....
0
 
BrandonGalderisiCommented:
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
 
iaing1000Author Commented:
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
 
BrandonGalderisiCommented:
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
 
jjerome00Commented:
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
 
BrandonGalderisiCommented:
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
 
3_SCommented:
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
 
iaing1000Author Commented:
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
 
iaing1000Author Commented:
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
 
BrandonGalderisiCommented:
Wow... A cursor?  Well at least you are only doing it for a migration.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now