SQL - Joining multiple records to one record

Question

I've got a SQL Server database with the the following tables:

Client (ClientID, ClientName)

SalesAgent (AgentID, AgentName)

Item (ItemID, Description)

Purchase (PurchaseID, ClientID, ItemID, Price)

PurchaseSalesAgent (PurchaseID, AgentID)

Each purchase is only ever one item to one client but there can have been multiple agents involved. I want to return the following list of columns:

ClientName, Description, Price, Agents

Where Agents is the names of all the agents involved in the purchase. Either as a comma separated list or as multiple columns with one agent in each.

I'm looking for a way that's compatible with SQL Server 2000 but I'd also be interested in if there's a better way of doing it in SQL Server 2008.

Answer 1

In SQL 2008, you should use FOR XML PATH('')... something like:

select c.clientname, i.description, p.price, 
   stuff((select ', ' + a.AgentName 
    from SalesAgent sa join PurchaseSalesAgent psa on psa.AgentID = sa.AgentID
    where psa.PurchaseID = p.purchaseID
    for xml path('')),1,2,'') as Agents
from client c 
join purchase p on p.clientid = c.clientid
join item i on i.itemid = p.itemid
;

In SQL 2000, there's no nice way of doing it.

Answer 2

Using Sql Server 2008, you can try

DECLARE @Client TABLE (ClientID INT, ClientName VARCHAR(10))

DECLARE @SalesAgent TABLE (AgentID INT, AgentName VARCHAR(10))

DECLARE @Item TABLE (ItemID INT, Description VARCHAR(10))

DECLARE @Purchase TABLE (PurchaseID INT , ClientID INT, ItemID INT, Price FLOAT)

DECLARE @PurchaseSalesAgent TABLE (PurchaseID INT, AgentID INT)


SELECT  c.ClientName,
        i.Description, 
        p.Price     ,
        (
            SELECT  sa.AgentName + ','
            FROM    @SalesAgent sa
            WHERE   sa.AgentID = pa.AgentID
            FOR XML PATH('')
        ) Agents
FROm    @Client c INNER JOIN
        @Purchase p ON c.ClientID = p.ClientID INNER JOIN
        @PurchaseSalesAgent pa ON p.PurchaseID = pa.PurchaseID INNER JOIN
        @Item i ON p.ItemID = i.ItemID

EDIT

I made the assumption that Purchase TABLE has a column ItemID INT to link to the Items TABLE