WoodyGuthrie
MIS
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.
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
Code:
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')
Code:
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.guidControlAssociateIDthe 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
