Our customers have a process where the lowest level of the organization submits weekly activity statuses up the chain. Each higher level may have 1 or more reporting sub levels and would require them to compile the results before submitting to their superiors. They have, for the longest time, been using email as their means of submission. The reports would come in as either messages in the email itself or Word documents attached to the email. There was no standardization among them. They would take hours cutting and pasting the comments before submitting up the ladder.

So, they came to us asking if their was a better way. We created a web application that displays a standard entry form with categories and text fields for each level to fill out. Once they submit the form, the data is collected in the tables. As the next level logs into the web application, the data from the tables would be compiled and then displayed to the screen.

In order to accomplish this, we use a Scalar-valued Function. The function receives attributes passed from the application and returns the compiled string to display. Here is how it works:

Table
UnitID   Date      CategoryID   CategoryContent   
1   6/13/2011   1      Unit 1 Cat 1
2   6/13/2011   1      Unit 2 Cat 1
3   6/13/2011   1      Unit 3 Cat 1
4   6/13/2011   1      Unit 4 Cat 1
1   6/13/2011   2      Unit 1 Cat 2
2   6/13/2011   2      Unit 2 Cat 2
3   6/13/2011   2      Unit 3 Cat 2
4   6/13/2011   2      Unit 4 Cat 2

Inside your cfquery tag you would include the function call like this

<cfquery name="CompileComments" datasource"YourDataSource">
SELECT DISTINCT
   UnitID, dbo.fnCompileComments('6/13/2011', '1', CategoryID) AS CompiledComments
FROM   TableName
WHERE   Date = '6/13/2011' AND UnitID = 1
</cfquery>

Function name in SQL is dbo.fnCompileComments. List the variables you are passing it and then declare the return Variable with AS.

The SQL function looks like this:

USE [DatabaseName]
GO
/****** Object: UserDefinedFunction [dbo].[fnCompileComments] Script Date: 06/13/2011 13:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fnCompileComments]
(
   @MYDate DATE,
   @MYUnitID INT,
   @MYCategoryID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
   DECLARE @Comments VARCHAR(MAX)

SELECT @Comments = COALESCE(@Comments + char(10) + char(13) + '|', + '') + CategoryContent
FROM   TableName
WHERE Dater = @MyDate AND
   UnitID = @MyUnitID AND
CategoryID = @MyCategoryID

RETURN @Comments

END

The expected result:
Unit 1 Cat 1
|Unit 2 Cat 1
|Unit 3 Cat 1
|Unit 4 Cat 1

Unit 1 Cat 2
|Unit 2 Cat 2
|Unit 3 Cat 2
|Unit 4 Cat 2

The user defined function uses the COALESCE function to compile the comments together. I use a char(10) and a char(13) to put a return line and carriage return between each comment. I also include a unique character "|" that I use for a specfic function elsewhere in the application.