Flattening Out SalesLogix Data To Combine Multiple Rows into a Single String using SQL

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.

Want content like this delivered to your inbox? Sign up for our newsletter!
ABOUT THE AUTHOR

Ryan Farley

Ryan Farley is the Director of Development for Customer FX and creator of slxdeveloper.com. He's been blogging regularly about SalesLogix, now Infor CRM, since 2001 and believes in sharing with the community. His new passion for CRM is Creatio, formerly bpm'online. He loves C#, Javascript, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

1 Comment

  1. 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
    (
    @contactid varchar(12)
    )
    returns varchar(8000)
    as
    begin

    declare @list varchar(8000)
    select
    @list = COALESCE(@list + ‘,’,”) + convert(varchar, a.startdate, 101) + ‘ ‘ + a.description
    from
    sysdba.activity a
    where
    a.contactid = @contactid
    order by
    a. startdate

    return @list

    end

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *