Syntax problem in PIVOT

• DaveBriCam

  SSCarpal Tunnel

  Points: 4302

  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;

• ratbak

  Default port

  Points: 1418

  More actions

  December 14, 2020 at 5:58 pm

  #3820360

  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: 4302

  More actions

  December 14, 2020 at 6:40 pm

  #3820386

  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

  Default port

  Points: 1418

  More actions

  December 14, 2020 at 9:43 pm

  #3820456

  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)