pivot query issue

• zenloki

  SSC Veteran

  Points: 218

  More actions

  April 21, 2022 at 4:36 pm

  #4020076

  I got my query working mostly but would like to add a row total at the end in the pivot

  here is my query, some of you experts might see what Im missing?

   

  --get row names

  DECLARE

  @columns NVARCHAR(MAX) = '',

  @sql NVARCHAR(MAX) = '';

  -- select the category names

  SELECT

  @columns+=QUOTENAME(DepartmentName) + ','

  FROM

  DepartmentTable

  ORDER BY

  DepartmentName;

  -- remove the last comma

  --set@colums variable

  SET @columns = LEFT(@columns, LEN(@columns) - 1);

  --get columns

  --PRINT @columns;

  -- construct dynamic SQL

  SET @sql ='

  SELECT * FROM

  (

  select

  JobCode,

  DepartmentName,

  (COALESCE(MonTime, 0)+COALESCE(TueTime, 0)+COALESCE(WenTime, 0)+COALESCE(ThurTime, 0)+COALESCE(FriTime, 0)

  +COALESCE(SatTime, 0)+COALESCE(SunTime, 0)) as total

  from TimeSheetTable

  INNER JOIN DepartmentTable ON TimeSheetTable.DeptId=DepartmentTable.Id

  inner join JobCodeTable on TimeSheetTable.JobId=JobCodeTable.Id

  ) t

  PIVOT(

  sum(total)

  FOR DepartmentName IN ('+ @columns +')

  )pivot_table

  ORDER BY JobCode

  '

  ---- execute the dynamic SQL

  EXECUTE (@sql);

   

  • This topic was modified 2 years, 3 months ago by  zenloki.

• ratbak

  Ten Centuries

  Points: 1345

  More actions

  April 21, 2022 at 7:10 pm

  #4020168

  Right now, you're missing the entire query

  🙂

• zenloki

  SSC Veteran

  Points: 218

  More actions

  April 21, 2022 at 8:16 pm

  #4020177

  File did not load sorry