Wednesday, 17 March 2010

Selecting a delimited list of rows with other values in SQL

There are a number of really detailed posts out there describing an easy way to concatenate the values from a specific column into a delimited list without resorting to a cursor. Using coalesce and a temporary variable, the activity is child’s play:

Assuming you have a table named MyTable with a column named FirstName:

DECLARE @List varchar(MAX)

SELECT 
        @List = COALESCE(@List + ', ', '') + CAST (FirstName as varchar (200))
FROM
        MyTable

select @List

the above will return you something like “Ben, James, Ed”.

Nice and easy, right? But how do you use this in a non-trivial situation where you need to include additional columns in the select list? You can’t do this in the above statement (as far as I’m aware); in my case today, I wanted to a) avoid declaring the temporary variable and b) join the results to another table by Id.

for xml path ('') will help in the first case:

select cast(FirstName as varchar (200)) + ', ' from MyTable
for xml path ('')

but you’ll still get stuck trying to join the results.

Thankfully Arnie Rowland has a really concise SQL wiki post describing how accomplish this feat in SQL Server 2000 and 2005/2008.

Assuming this table:

CREATE TABLE TeamInfo
( MemberID int IDENTITY,
TeamID int,
FirstName varchar(50)
)


Arnie’s query looks like this:



SELECT
t1.TeamID,
MemberList = substring((SELECT ', ' + FirstName
FROM TeamInfo t2
WHERE t1.TeamID = t2.TeamID
ORDER BY
TeamID,
FirstName
FOR XML PATH('')
), 3, 1000 )FROM TeamInfo t1
GROUP BY TeamID


And returns this result:



TeamID     MemberList
1 Bob, Jim, Mary
2 Ellen, Ralph, Sue
3 Bill, Linda


Note the substring function drops the leading delimiter and limits the result to just under 1000 characters. Also TeamInfo doesn’t have to be the same table, it can be any table with same IDs used in the t2 query.

No comments:

Post a comment

Spam comments will be deleted

Note: only a member of this blog may post a comment.