I have an SQLserver normallized db and have a requirement that could possibly be handled by a single tsql pivot table query which I'll put in an SqlDataSource in VS2008. I got syntax & samples but sample uses a simplified single unnormalized table as the source. When I try to implement my multiple-join query, I don't get anywhere. My query is:SELECT Users.NameLast, ContractMaster.ContractName + ' ' + ClinMaster.ClinName AS ContractClin, ClinItemEmpXref.EmpShare FROM EmpClinShare INNER JOIN ClinItemEmpXref ON EmpClinShare.CDUID = ClinItemEmpXref.CDUID INNER JOIN CLINdetail ON ClinItemEmpXref.ItemID = CLINdetail.ItemID INNER JOIN ClinMaster ON CLINdetail.ClinID = ClinMaster.ClinID INNER JOIN ContractMaster ON ClinMaster.ContractID = ContractMaster.ContractID RIGHT OUTER JOIN Users ON ClinItemEmpXref.UserID = Users.UserID WHERE (EmpClinShare.ProjMo = '2/1/2010') ORDER BY Users.NameLast, ContractClin
giving this result (sample):NameLast Contract ShareBarber 4.2.5 0.5Brotherton 4.2.1.2 1Caddell C130 0.8Caira NDAC 0.2Caira PTA 0.4Caira TEFBS 0.1
This is how I tried to turn my join query into a pivot table query:SELECT Users.NameLastFROM EmpClinShare INNER JOIN ClinItemEmpXref ON EmpClinShare.CDUID = ClinItemEmpXref.CDUID INNER JOIN CLINdetail ON ClinItemEmpXref.ItemID = CLINdetail.ItemID INNER JOIN ClinMaster ON CLINdetail.ClinID = ClinMaster.ClinID INNER JOIN ContractMaster ON ClinMaster.ContractID = ContractMaster.ContractID RIGHT OUTER JOIN Users ON ClinItemEmpXref.UserID = Users.UserIDPIVOT( SUM(EmpClinShare.EmpShare) FOR Users.NameLast IN ([ClinMaster.ClinName]))AS p
but it errors out. This is what I want: NameLast 4.2.5 4.2.1.2 C130 NDAC PTA TEFBSBarber 0.5 Brotherton 1 Caddell 0.8 Caira 0.2 0.4 0.1
Can anyone help me turn my join query into a pivot table query? Thank you!
My "50 First Dates" with a pivot table tsql query
Posted - 2010-04-08 : 12:40:26
