Friday, September 24, 2010

Getting Rows of data as a single column in a SQL query

Recently, I've been working on a Sharepoint Based document workflow solution that uses a custom SQL database. I ran into an issue where I needed to join a database table and get many rows of data back as one column in a query.

For example:
Table A:
ID, Name,

Table B:
childName, ParentID

where parent ID is table A.ID

I needed my results to be in the format:
A.ID, name, (childName1, ChildName2,ChildName3,....)

I found a few options,

Provided about a dozen different ways to do it. All of them looked overly complex.

Then i stumbled upon:

which provided a simple Scalar function to return the results. I took that method and put together a SQL Scalar function to do it:

CREATE FUNCTION GetChildrenFromParent

@ParentID varchar(15)
RETURNS varchar(5000)

DECLARE @ChildList varchar(5000)

ChildList = ''

select @
ChildList = coalesce(@ChildList + ', ', '') + childName from B where ParentID=@ParentID

-- Return the result of the function

Then my SQL to get the data looks something like:
select *,GetChildrenFromParent(id) as ChildNames from A

This returns results like:

1|Jeff| andy,mark,shannon

so, the one column is a comma separated list of rows of data from another table.