[RESOLVED] Jet Query to T-SQL Query Help

[FishGuy]

I wrote a Crosstab query in access and now want it to run as an sp on SQL Server, I know T-SQL dosent support TRANSFORM and Pivot and have previously got round this using CASE. However I picked up a utility to transform my original jet SQL to T-SQL and seems to just utilise aliases and group by. However the SQL generates an error "The Identifier that starts with 'Select ------ is too long must.maximum of 128" I would really like this to work as there will be a varying amount of columns as the data changes so CASE doesnt seem appropriate.
Here is my origional Jet SQL-

TRANSFORM Sum(a.IssueValue) AS SumOfIssueValue
SELECT [AgentFirst] & " " & [AgentSurname] AS Agent, Sum(a.IssueValue) AS [Total Of IssueValue]
FROM [SELECT TBL_LK_Agents.AgentID, TBL_LK_Agents.AgentSurname, TBL_LK_Agents.AgentFirst, TBLAds.IssueValue, TBLAds.IssueNo
FROM TBL_LK_Agents INNER JOIN ((TBLSales INNER JOIN TBLAds ON TBLSales.SaleNo = TBLAds.SaleNo) INNER JOIN TBLClientCompany ON TBLSales.CompanyNo = TBLClientCompany.CompanyNo) ON TBL_LK_Agents.AgentID = TBLSales.SaleMadeBy
WHERE TBLSales.Cancelled = 0
GROUP BY TBL_LK_Agents.AgentID, TBL_LK_Agents.AgentSurname, TBL_LK_Agents.AgentFirst, TBLAds.IssueNo,TBLAds.IssueValue]. AS a
GROUP BY [AgentFirst] & " " & [AgentSurname]
PIVOT a.IssueNo;


And Here is my T-SQL
CREATE PROCEDURE sp_IssueTotalsCross
AS SELECT [AgentFirst] + ' ' + [AgentSurname] AS Agent,
a.IssueNo, SUM(a.IssueValue) AS SumOfIssueValue
FROM
[SELECT TBL_LK_Agents.AgentID, TBL_LK_Agents.AgentSurname, TBL_LK_Agents.AgentFirst, TBLAds.IssueValue, TBLAds.IssueNo
FROM TBL_LK_Agents
INNER JOIN ((TBLSales
INNER JOIN TBLAds
ON TBLSales.SaleNo = TBLAds.SaleNo)
INNER JOIN TBLClientCompany
ON TBLSales.CompanyNo = TBLClientCompany.CompanyNo)
ON TBL_LK_Agents.AgentID = TBLSales.SaleMadeBy
WHERE TBLSales.Cancelled = 0
GROUP BY TBL_LK_Agents.AgentID, TBL_LK_Agents.AgentSurname, TBL_LK_Agents.AgentFirst, TBLAds.IssueNo,TBLAds.IssueValue], a.IssueNo
ORDER BY TBL_LK_Agents

Any help much appreciated

[PilgrimPete]

Hi. I'm intrigued by three things here - the period after the subquery in your Jet SQL, the use of square brackets in the T-SQL query, and the reason/use for "a.IssueNo".
I'd try something like this first:

CREATE PROCEDURE sp_IssueTotalsCross 
AS 
SELECT [AgentFirst] + ' ' + [AgentSurname] AS Agent,
a.IssueNo, SUM(a.IssueValue) AS SumOfIssueValue
FROM 
(SELECT TBL_LK_Agents.AgentID, TBL_LK_Agents.AgentSurname, TBL_LK_Agents.AgentFirst, TBLAds.IssueValue, TBLAds.IssueNo 
FROM TBL_LK_Agents
INNER JOIN ((TBLSales 
INNER JOIN TBLAds 
ON TBLSales.SaleNo = TBLAds.SaleNo) 
INNER JOIN TBLClientCompany
ON TBLSales.CompanyNo = TBLClientCompany.CompanyNo) 
ON TBL_LK_Agents.AgentID = TBLSales.SaleMadeBy 
WHERE TBLSales.Cancelled = 0
GROUP BY TBL_LK_Agents.AgentID, TBL_LK_Agents.AgentSurname, TBL_LK_Agents.AgentFirst, TBLAds.IssueNo,TBLAds.IssueValue) AS a
ORDER BY TBL_LK_Agents

and see if that progresses things any...

[PilgrimPete]

Oh, and the ORDER BY clause is a bit iffy too...

ORDER BY TBL_LK_Agents

I assume that TBL_LK_Agents is a table. Which field do you want to sort by?

[FishGuy]

Thanks I tried your suggestion which has cleared the previous error and I have got rid of the order by just to simplify it for the moment, I now get the error that my columns a.AgentFirst etc are invalid in the select list because they are not contained in an aggregate function or there is no Group By clause.

[FishGuy]

Just so Its easier to see what I want to acheive someting like this


Agent Issue 1 Issue 2 Issue 3 etc
Jon £100 £200 £50
Bill £200 £300 £0

[PilgrimPete]

Have you tried adding a GROUP BY Clause?
Something like:

GROUP BY a.AgentFirst, a.AgentSurname, a.IssueNo

[FishGuy]

Thanks for your help Pete I left the query as Jet SQL in the end in an access database then let my colleage use it from there as a datasource. Not ideal but it wont be run very often anway, thanks for your help though.