SQL is often overlooked for programming capabilities. The concatenation function below saved me a lot of time in application programming to simplify presenting a 1-Many data relationship.  This function has been extremely useful for me in report writing, data presentation , etc.

 
The function takes a group of related records and returns a csv of the multiple rows
 
Example…
 
 
Account(Parent Table) ParentID     Name
1    ABC
2    DEF
 
 
Contacts(1-Many Child Table)
ParentID    ContactID    LastName
1    1    Johnson
1    2    Doe
2    3    Smith
2    4    Adams
2    4    Jones
 
Query using the function…
Select Name, ConcatContacts(ParentID) as Contacts from Account
 
Returns
Name    Contacts
ABC    Johnson, Doe
DEF    Smith, Adams, Jones
 
 
 
SQL Script to create function
 
/********Script Function Begin***********/
 
USE [CMRX_SLX626Prod_JM]
GO
 
/****** Object:  UserDefinedFunction [dbo].[ConcatContacts]    Script Date: 06/17/2011 14:48:01 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
 
CREATE FUNCTION [SYSDBA].[ConcatContacts](@ID CHAR(12))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''
 
SELECT @Output =        CASE @Output WHEN '' THEN LastName ELSE @Output + ', ' + LastName
END
FROM CONTACTS
WHERE PARENTID = @ID ORDER BY CONTACTID
 
RETURN @Output
END
 
 
 
GO
 
 
/********Script Function End***********/