May 20, 2009 at 10:49 am
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. I've never used a pivot or crosstab in TSQL, but no aggregations are necessary so I'm not sure those methods would apply here.
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
CharlestonFreezerBWA080612091455 58891Bryan Waton
CharlestonFreezerBWA080612091455 58891Damion Smith
For Each Receipt I would like to be able to return the row like this:
Facility, Dept, Receipt, PO, TeamLoad, Associate1, Associate2
CharlestonFreezerBWA080612091455 58891Bryan Waton Damion Smith
Any given Receipt may have one or many Associates. I'm thinking maybe there is some way to use a count of associates on each Receipt and populate that number of columns (Associate1, Associate2, Associate3, etc.) for all Associates related to that Receipt.
Any help is greatly appreciated.
May 20, 2009 at 11:00 am
Pardon the "Let Me Google That For You", but here are some places for you to start your research:
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply