Is there a way to return multiple results with a subquery?

Question

I have need to return multiple results from a subquery and have been unable to figure it out. The end result will produce the persons name across the vertical axis, various actions based on an action category across the horizontal axis. So the end result looking like:

----------
**NAME            CATEGORY 1             CATEGORY 2**

Smith, John     Action 1, Action 2     Action 1, Action 2, Action 3


----------

Is there a way to do this in a single query?

select
   name,
   (select action from actionitemtable where actioncategory = category1 and contact = contactid)
from
   contact c
   inner join actionitemtable a
     on c.contactid = a.contactid

If more than one result is returned in that subquery I would like to be able to display it as a single comma separated string, or list of actions, etc.

Thank you.

Microsoft Sql Server 2005 is being used.

Answer 1

I use a User Defined Function for this task. The udf creates a delimited string with all elements matching the parameters, then you call the udf from your select statement such that you pull a delimited list for each record in the recordset.

CREATE FUNCTION dbo.ud_Concat(@actioncategory int, @contactid int)
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @sOutput VARCHAR(8000)
    SET @sOutput = ''

    SELECT @sOutput = COALESCE(@sOutput, '') + action + ', '
    FROM dbo.actionitemtable
    WHERE actioncategory=@actioncategory AND contact=@contact 
    ORDER BY action

    RETURN @sOutput
END

SELECT 
   name, 
   dbo.ud_Concat(category1, contactid) as contactList
FROM contact c
INNER JOIN actionitemtable a ON c.contactid = a.contactid