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

Question

I need to return some results from a subquery and could not figure it out. The end result will produce the person’s name on the vertical axis, various actions based on the category of actions on the horizontal axis. So, the end result is as follows:

----------
**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 request?

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 this subquery, I would like to display it as a single line, separated by commas, or a list of actions, etc.

Thanks.

Used by Microsoft Sql Server 2005.

Answer 1

User Defined . Udf , , udf select, .

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 2

, .

:

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

:

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

(2 row(s) affected)

Answer 3

. " ", ( ), : ? "", , . , SQL, .

Answer 4

:

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]

, , .

EDIT:, , [AdditionalData] NULL.

Answer 5

, , . Microsoft .

Answer 6

, , . , DB Adventureworks:

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

select:

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