How to convert several rows into a CSV string

Scenario

I recently posted just about the opposite (see INNER JOIN with a comma separated values CSV field ) and now I want to show how to create a CSV list when you have several rows to merge, grouping by some criteria. Let's suppose you have a set of data such as:

F1  F2  Item
-----------------
1  4    A
1  4    B
3  2    A
3  3    C
5  1    B
5  4    A
5  4    E
5  4    F
6  1    E
6  1    W

And you need to generate something like:

F1  F2  ItemsList
-----------------
1  4    A, B
3  2    A
3  3    C
5  1    B
5  4    A, E, F
6  1    E, W

Solution

This procedure is based on the information I found on Converting Multiple Rows into a CSV String (Set Based Method) but tuned to be able to group by a set of fields, not only one:

IF NOT OBJECT_ID('tempdb..#Source') IS NULL
  DROP TABLE #Source
IF NOT OBJECT_ID('tempdb..#t') IS NULL
  DROP TABLE #t

SELECT 1 F1, 4 F2, 'A' Item INTO #Source
UNION ALL SELECT 1, 4, 'B'
UNION ALL SELECT 3, 2, 'A'
UNION ALL SELECT 3, 3, 'C'
UNION ALL SELECT 5, 1, 'B'
UNION ALL SELECT 5, 4, 'A'
UNION ALL SELECT 5, 4, 'E'
UNION ALL SELECT 5, 4, 'F'
UNION ALL SELECT 6, 1, 'E'
UNION ALL SELECT 6, 1, 'W'

CREATE TABLE #t (
  F1 int not null,
  F2 int not null,
  Item varchar(7) not null,
  ItemsList varchar(8000)
)

INSERT INTO #t (F1, F2, Item)
  SELECT TOP (100) PERCENT F1, F2, Item
  FROM #Source
  ORDER BY F1, F2, Item

DECLARE
    @List varchar(8000),
    @F1_Last int,
    @F2_Last int

SELECT
    @List = '',
    @F1_Last = -1,
    @F2_Last = -1

UPDATE #t
  SET
    @List = ItemsList =
      CASE
        WHEN (@F1_Last <> F1) OR
             (@F2_Last <> F2)
          THEN Item
        ELSE @List + ', ' + Item
      END,
    @F1_Last = F1,
    @F2_Last = F2

SELECT TOP (100) PERCENT F1, F2,
  CASE
    WHEN LEN(MAX(ItemsList)) > 512
      THEN CONVERT(varchar(512), LEFT(MAX(ItemsList), 509) + '...')
    ELSE CONVERT(varchar(512), MAX(ItemsList))
    END AS ItemsList
FROM #t
GROUP BY F1, F2
ORDER BY F1, F2
GO

Regarding efficency I must say that using this approach in my production environment, where the original internal query (real table instead of #Source temporary table) would return near 5800 rows, 5 columns, needed grouping by 4 of them (integers) and creating a list combining the fifth column (varchar(512)), this procedure does the job in just 3 seconds, whereas our old iterative-cursor-based procedure took more than 23 seconds to complete.

Outlook 2007 Business Contact Manager served by SQL Server 2005

My last post dealed with installing BCM 2003 on a remote dedicated server; in this case, the same subject but related to the new Office Outlook 2007 with Business Contact Manager is addressed here. Microsoft has this procedure fully documented on Outlook 2007 Document: Deploying Outlook 2007 with Business Contact Manager in a Remote Database Configuration

Outlook 2007 with Business Contact Manager uses a SQL Server 2005 database for storing its data. By default, configuring Outlook 2007 with Business Contact Manager with any Outlook profile creates a SQL database on the local computer. Instead of creating a database on the local computer, you can choose to connect to an existing database on a remote server.

The main quiz of the document is installing a named instance called MSSMLBIZ and set up to listen to TCP port 5356. The documents talks about moving the database data using a backup and a restore, but if you want to try other procedure, you can simply:

1. Stop MSSQL$MSSMLBIZ service running on the original PC
2. Copy database files (called MSSmallBusiness.ldf and MSSmallBusiness.mdf by default on BCM2007) to the new coputer that will be serving them
3. Attach the database files (using sp_attach_db or the IDE in VS2005)
4. Configure proper permissions, open ports, etc.

The most useful point in the former Microsoft document (IMHO) is the one that explains how to Use a database backup file from a previous version of Business Contact Manager for Outlook on a remote server.

Users may have used a previous version of Business Contact Manager for Outlook 2003, and may have created a backup of their database from that previous version. Specifically, the backup file may have been created by using the user interface and it may be in an .msbcm file or a .sbb file. This section describes how these backup files may be restored to a Business Contact Manager 2007 database on a remote server.

Happy upgrading ;)