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