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.