pivot query issue

  • 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 3 weeks, 5 days ago by  zenloki.
  • Right now, you're missing the entire query

    🙂

  • File did not load sorry

     

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

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