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***********/
Posted in: