Hi,
I came across this problem ,
select * from
(select distinct amountfc, amounttype, Date_,SupplierCode,InvoiceType,
CostType,Total,
Currency,ROE from
invoice inner join inv_detailed on
invoice.InvoiceID=inv_detailed.InvoiceID ) as res
PIVOT (
sum(AmountFC) for AmountType in ( select amounttype from inv_amounttypes )
) as test
END
and yes, i understood this cannot be done, well it is one of the most unbelievable and obvious barrier to programming, for i did not see hardcoding a field name a must before. this implementation could have been PIVOT (
sum(AmountFC) for AmountType in ('a','b','c') as test allowing for more flexibility(i talked about the single quote) and i would be seeing the select working fine too with such an implementation, so what were those working on the pivot function thinking about?
Discussion on:
View:
Show:
SELECT dbo.tbEmployee.Service_Number, dbo.tbEmployee.Designation, dbo.tbEmployee.Emp_ID, dbo.tblEmployeePay.Value, dbo.tblEmployeePay.Year,
dbo.tblEmployeePay.Month, dbo.tblEmployeePays.TotalDeductions, dbo.tblEmployeePays.Arears, dbo.tblEmployeePays.TotalEntitlements,
dbo.tblEmployeePays.TotalPayment, dbo.tbEmployee.Emp_Name, dbo.tblEmployeePay.HeadID, dbo.tblPayHeads.Name
FROM dbo.tbEmployee INNER JOIN
dbo.tblEmployeePay ON dbo.tbEmployee.Emp_ID = dbo.tblEmployeePay.EmpID INNER JOIN
dbo.tblPayHeads ON dbo.tblEmployeePay.HeadID = dbo.tblPayHeads.HeadID INNER JOIN
dbo.tblEmployeePays ON dbo.tblEmployeePay.EmpID = dbo.tblEmployeePays.Emp_ID AND
dbo.tblEmployeePay.Year = dbo.tblEmployeePays.Year AND dbo.tblEmployeePay.Month = dbo.tblEmployeePays.Month
WHERE (dbo.tblEmployeePay.HeadID 7) AND (dbo.tblEmployeePay.Year = 2007) AND (dbo.tblEmployeePay.Month = 1)
i need dbo.tbEmployee.Service_Number, dbo.tbEmployee.Designation, dbo.tblEmployeePay.Value,dbo.tblEmployeePays.TotalDeductions, dbo.tblEmployeePays.Arears, dbo.tblEmployeePays.TotalEntitlements,
dbo.tblEmployeePays.TotalPayment, in pivvot sequence in End result
dbo.tblEmployeePay.Month, dbo.tblEmployeePays.TotalDeductions, dbo.tblEmployeePays.Arears, dbo.tblEmployeePays.TotalEntitlements,
dbo.tblEmployeePays.TotalPayment, dbo.tbEmployee.Emp_Name, dbo.tblEmployeePay.HeadID, dbo.tblPayHeads.Name
FROM dbo.tbEmployee INNER JOIN
dbo.tblEmployeePay ON dbo.tbEmployee.Emp_ID = dbo.tblEmployeePay.EmpID INNER JOIN
dbo.tblPayHeads ON dbo.tblEmployeePay.HeadID = dbo.tblPayHeads.HeadID INNER JOIN
dbo.tblEmployeePays ON dbo.tblEmployeePay.EmpID = dbo.tblEmployeePays.Emp_ID AND
dbo.tblEmployeePay.Year = dbo.tblEmployeePays.Year AND dbo.tblEmployeePay.Month = dbo.tblEmployeePays.Month
WHERE (dbo.tblEmployeePay.HeadID 7) AND (dbo.tblEmployeePay.Year = 2007) AND (dbo.tblEmployeePay.Month = 1)
i need dbo.tbEmployee.Service_Number, dbo.tbEmployee.Designation, dbo.tblEmployeePay.Value,dbo.tblEmployeePays.TotalDeductions, dbo.tblEmployeePays.Arears, dbo.tblEmployeePays.TotalEntitlements,
dbo.tblEmployeePays.TotalPayment, in pivvot sequence in End result
what is the query to get the fields and datatypes of a particular table
in SQL 2000 we r using to write queries Query Analyser. what is it in 2005 where i want to write in queries in 2005
i want to know how can i update my any created pivot report with sql server,pl. revert back on email id : koushi.guha07@gmail.com
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
