Syntax problem in PIVOT

Question

Syntax problem in PIVOT

DaveBriCam

SSCarpal Tunnel

Points: 4298
More actions
December 14, 2020 at 4:34 pm

#3820331
I'm having a syntax problem with the following, any help much appreciated! It doesn't like the "[TraceyHarley5],[RhonaCornwall5]" in the first line, "(CALL_TIME" in the 6th line and "[TraceyHarley5]" in the 7th line:
```
SELECT 'TotalTimeByAgent' AS TotalTime, [TraceyHarley5],[RhonaCornwall5] 
FROM (SELECT Agent, time 
FROM [a2wh].[dbo].[CallLogCommon] with (NOLOCK)) as AgentTable
PIVOT
(
SUM(dbo.fnGetSeconds(CALL_TIME) AS Call_Time_Seconds)) 
FOR AGENT IN ([TraceyHarley5],[RhonaCornwall5])
) AS PivotTable;
```

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

Your subquery is not returning TraceyHarley5, RhonaCornwall5 , or CALL_TIME, so the select, function, & PIVOT have no reference to those columns. And the only columns in your subquery -- Agent, time -- are not used anywhere else.

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 2

then it seems like this should work:

SELECT * FROM (
SELECT  agent, dbo.fnGetSeconds(CALL_TIME) 
FROM 
    [a2wh].[dbo].[CallLogCommon] 
) t
pivot (
    SUM(dbo.fnGetSeconds(CALL_TIME))
    FOR agent IN (
       [TraceyHarley5],[RhonaCornwall5])
) AS pivot_table;

ratbak Ten Centuries Points: 1358 More actions · Answer 3

I'm guessing it didn't.... You still aren't returning CALL_TIME in the subquery, but are trying to reference it in the pivot.

I believe that in your select, you would need to return the aliased function result in your subquery -- e.g.,

dbo.fnGetSeconds(CALL_TIME) AS CallTimeSeconds

and then reference that in the PIVOT -- e.g.,

SUM(CallTimeSeconds)