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

you need to give more info about your table structure and how they join to each other.

here is a generic example about combining multiple rows into a single column:

declare @table table (name varchar(30)
                     ,ID int
                     ,TaskID char(3)
                     ,HoursAssigned int
                     )

insert into @table values ('John Smith'   ,4592 ,'A01'  ,40)
insert into @table values ('Matthew Jones',2863 ,'A01'  ,20)
insert into @table values ('Jake Adams'   ,1182 ,'A01'  ,100)
insert into @table values ('Matthew Jones',2863 ,'A02'  ,50)
insert into @table values ('Jake Adams'   ,2863 ,'A02'  ,10)


SELECT DISTINCT
    t1.TaskID
       ,SUBSTRING(
                  replace(
                          replace(
                                  (SELECT
                                       t2.Name
                                       FROM @Table AS t2
                                       WHERE t1.TaskID=t2.TaskID
                                       ORDER BY t2.Name
                                       FOR XML PATH(''))
                                 ,'</NAME>','')
                         ,'<NAME>',', ')
                 ,3,2000)  AS PeopleAssigned
    FROM @table AS t1

OUTPUT:

TaskID PeopleAssigned
------ --------------------------------------
A01    Jake Adams, John Smith, Matthew Jones
A02    Jake Adams, Matthew Jones

(2 row(s) affected)

Answer 2

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

Answer 3

You will probably have to create a custom aggregate function. Microsoft has a knowledge base article with sample code here.

Answer 4

If you don't mind using cursors, you can write your own function to do this. Here's an example that will work on the Adventureworks sample DB:

CREATE FUNCTION CommaFunctionSample 
(
    @SalesOrderID int
)
RETURNS varchar(max)
AS
BEGIN

DECLARE OrderDetailCursor CURSOR LOCAL FAST_FORWARD
FOR
SELECT SalesOrderDetailID
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = @SalesOrderID

DECLARE @SalesOrderDetailID INT

OPEN OrderDetailCursor

FETCH NEXT FROM OrderDetailCursor INTO @SalesOrderDetailID
DECLARE @Buffer varchar(max)
WHILE @@FETCH_STATUS = 0
BEGIN
 IF @Buffer IS NOT NULL SET @Buffer = @Buffer + ','
 ELSE SET @Buffer = ''

 SET @Buffer = @Buffer + CAST(@SalesOrderDetailID AS varchar(12))

 FETCH NEXT FROM OrderDetailCursor INTO @SalesOrderDetailID
END

CLOSE OrderDetailCursor
DEALLOCATE OrderDetailCursor

RETURN @Buffer
END

This is how such a function would appear in a select query:

SELECT AccountNumber, dbo.CommaFunctionSample(SalesOrderID)
FROM Sales.SalesOrderHeader

Answer 5

This is pretty abstract and complex. My initial reaction was "pivot query", but the more I looked at it (and at the earlier responses) the more I thought: Can you pass this one off to the application team? You return the "base", and they write and apply the procedural code that makes this kind of problem a snap. Sure, you can squeeze it in to SQL, but that doesn't make it the right place to do the work.

Answer 6

According to your query try this:

SELECT [Name],
       STUFF(
         (
           SELECT ' ,' + [Action] 
           FROM   [AactionItemTable]
           WHERE  [ActionCategory] = category1 
                  AND [Contact] = contactid
           FOR XML PATH('')
         ), 1, 2, ''                
       ) AS [AdditionalData]
FROM   [Contact] C
       INNER JOIN [ActionItemTable] A
       ON C.[ContactId] = A.[ContactId]

Guess this is the simplest way to do what you want.

EDIT: if there is no action in the subquery found, the [AdditionalData] result will be NULL.