A question came up from a person attending our current Report Writing class about how to flatten out data that exists as multiple rows in the database to a single string in a report. There is a really cool trick you can do in SQL to accomplish this which I previously outlined here:
See Flattening Out Data with One of the Coolest SQL Tricks Ever
In this post from 2005 I outline a technique that can be used to make a query append each value from the rows in the query to a variable. Without using cursors, you will end up with a variable that has the values appended to it as one single string. Go read my original post to understand this technique further (I’ll wait).
Now, that you’ve read that, let’s look at a practical example of using that technique with SalesLogix Data so that it could be included in a report. In this sample, we will be combining all rows of activity data (the date and description only) into a single string for each contact. So, instead of having multiple rows of activities for each contact, we’ll end up with a string of activities like this: “3/2/2009 Lunch Meeting, 4/20/2009 Appoinement, 8/10/2009 Follow-up Call”.
There are two database objects we’ll need to create. First, we’ll create a SQL function. This function will take in a parameter of a ContactID and will return a string of activities for that contact. This function will look like this:
create function sysdba.GetActivityListForContact ( @contactid varchar(12) ) returns varchar(8000) as begin declare @list varchar(8000) set @list = '' select @list = @list + convert(varchar, a.startdate, 101) + ' ' + a.description + ', ' from sysdba.activity a where a.contactid = @contactid order by a. startdate set @list = rtrim(@list) if @list <> '' set @list = left(@list, len(@list) - 1) return @list end
Now that we have that function, we can use it in a query, or wrap it in a SQL view as follows:
create view sysdba.vContactExtended as select * , sysdba.GetActivityListForContact(contactid) as ActivityList from sysdba.contact
Now you can just add your vContactExtended view into the report and you’ll have the new ActivityList column added where you’ll see the activities listed all in a single field.
I had used a function similar to this recently…adding a COALESCE in there makes it a little tighter, as you can get rid of the other string functions:
create function sysdba.GetActivityListForContact
declare @list varchar(8000)
@list = COALESCE(@list + ‘,’,”) + convert(varchar, a.startdate, 101) + ‘ ‘ + a.description
a.contactid = @contactid