SQL server issue
The SQL below runs in MS Access, but fails when run in the SQL query analyser.
TRANSFORM First(qryWebCustomersAllParents.Parent) AS FirstOfParent SELECT tblCustomers.CustomerID, tblCustomers.CustNameAbbr AS Customer,
tblLUBudgetLevels_1.BudgetLevel, qryWebCustomerListCCRMs.CCRMPost,
qryWebCustomerListCCRMs.CCRMIncumbent, qryWebCustomerListCCRMs.PostEmailRLIAlias,
qryWebCustomerListCCRMs.PostTelephoneVPN
FROM qryWebCustomersAllParents
RIGHT JOIN ((tblCustomers LEFT JOIN tblLUBudgetLevels AS tblLUBudgetLevels_1
ON tblCustomers.CustBudgetLevelID = tblLUBudgetLevels_1.BudgetLevelID)
LEFT JOIN qryWebCustomerListCCRMs
ON tblCustomers.CustomerID = qryWebCustomerListCCRMs.CustomerID)
ON qryWebCustomersAllParents.CustHierCustomerID = tblCustomers.CustomerID WHERE (((tblCustomers.CustActive)='T')) GROUP BY tblCustomers.CustomerID, tblCustomers.CustNameAbbr, tblLUBudgetLevels_1.BudgetLevel,
qryWebCustomerListCCRMs.CCRMPost, qryWebCustomerListCCRMs.CCRMIncumbent,
qryWebCustomerListCCRMs.PostEmailRLIAlias, qryWebCustomerListCCRMs.PostTelephoneVPN
PIVOT qryWebCustomersAllParents.ParentBudgetLevel In ('TLB','HLB','IHLB','BLB');
In SQL I get:
Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'qryWebCustomersAllParents'.
Server: Msg 170, Level 15, State 1, Line 16 Line 16: Incorrect syntax near 'PIVOT'.
Does SQL Server support the TRANSFORM, First(), GROUP BY and PIVOT keywords?
Is there something obvious that would break it for SQL?
Software/Hardware used:
Software/Hardware used:
ASKED:
January 19, 2005 10:19 AM
UPDATED:
January 20, 2005 5:41 AM
Answer Wiki:
1) Does SQL Server support the TRANSFORM, First(), GROUP BY and PIVOT keywords?
First() is part of TRANSFORM syntax.
TRANSFORM and PIVOT do not exist in T-SQL.
GROUP BY does.
2) Is there something obvious that would break it for SQL?
In the BOL / Search for "pivot" you'll got an example of a cross-tab report.
To see all answers submitted to the Answer Wiki: View Answer History.
Thanks for response ebersphi. Have found this helpful too:
http://www.ftponline.com/archives/premier/mgznarch/vbpj/2001/10oct01/sqlpro0110/rj0110/rj0110-2.asp