Query with One-To-Many Help

[WoodyGuthrie]

I have the following sample data and query. The tblControl (cl) has a one to many relationship with tblControlAssociates (cla) and tblControlAssignments (cls). I need to be able to bring up all associates to the same row based on the Receipt.

Declare @Temp Table
(
  facility VarChar(25)
, Dept VarChar(25)
, Receipt VarChar(25)
, PO VarChar(25)
, TeamLoad bit
, Associate VarChar(50)
)

Insert Into @Temp Values('Charleston','Freezer','BWA080612091455','5889','1','Damian Smith')
Insert Into @Temp Values('Charleston','Freezer','BWA080612091455','5889','1','Bryan Waton')

SELECT  
 f.strName AS Facility
,d.strName AS Dept
,cl.strReceiptNo AS Receipt
,po.strPurchaseOrder AS PO
,cl.bitteamload AS TeamLoad
,cla.strName AS Associate


FROM   dbo.tblControlPurchaseOrders AS po
JOIN dbo.tblControl AS cl
ON po.guidControlID=cl.guidControlID
JOIN dbo.tblFacilities AS f
ON cl.guidFacilityID=f.guidFacilityID
JOIN dbo.tblDepartments AS d
ON cl.guidDepartmentID=d.guidDepartmentID
JOIN dbo.tblControlAssociates AS cla
ON cl.guidControlID=cla.guidControlID
JOIN dbo.tblControlAssignments AS cls
ON cla.guidControlAssociateID=cls.guidControlAssociateID

the query returns the following result:

Facility, Dept, Receipt, PO, TeamLoad, Associate
Charleston Freezer BWA080612091455 5889 1 Bryan Waton
Charleston Freezer BWA080612091455 5889 1 Damion Smith


For Each Receipt I would like to be able to return the row like this:

Facility, Dept, Receipt, PO, TeamLoad, Associate1, Associate2
Charleston Freezer BWA080612091455 5889 1 Bryan Waton Damion Smith


Any given Receipt may have one or many Associates. I'm thinking maybe there is some way to eithr use a count of associates on each Receipt (may with a tally) and populate that number of columns (Associate1, Associate2, Associate3, etc.) for all Associates related to that Receipt OR possibly use a pivot to dynamically populate variable number of columns.

Still researching, but could use a nudge. Any help is greatly appreciated.



Woody

 

[WoodyGuthrie]

I want to do this but, using set-based code. Has anyone done this using a tally table? Thanks.

http://www.tek-tips.com/faqs.cfm?fid=2146

Woody