sql-server - 如何使用每行的总计列来透视查询结果

Question

DECLARE @TimeByDay VARCHAR(max)

SET @TimeByDay = LTRIM(RTRIM(
  (
    SELECT QUOTENAME(REPLACE(LEFT(DATENAME(dw, TimeByDay), 3) + ', ' + CAST(TimeByDay AS varchar(11)),'''',''''''),'[') + ', '           
    FROM #TimesheetLineData
    GROUP BY TimeByDay
    ORDER BY TimeByDay
    FOR XML PATH('')
  )))
  SET @TimeByDay = LEFT(@TimeByDay, Len(@TimeByDay) - 1)
  --PRINT 'columns -' + @TimeByDay;

DECLARE @sql VARCHAR(MAX), @sql2 VARCHAR(MAX), @sql3 VARCHAR(MAX), @sql4 VARCHAR(MAX),@sql5 Varchar(Max),@sql6 Varchar(Max),@sql7 Varchar(Max),@sql8 Varchar(Max);

SET @sql = 'SELECT *,0 As OrderID 
        INTO ##fp_FetchTimeSheet_PWA_Data 
        FROM 
        ('

SET @sql3 = ' 
        SELECT ResourceName AS ResourceName
            , SOW
            , TaskName As [Task Name/Description]
            , ' + @TimeByDay + '

    FROM
    (
        SELECT 
        ResourceName,
        SOW,    
        TaskName,
        LEFT(DATENAME(dw, TimeByDay), 3) + '', '' + CAST(TimeByDay AS varchar(11)) AS TimeByDay,
        CAST(CAST(ActualWorkBillable as decimal(8,1)) as Varchar)+ '' h''  AS PlannedWork
        FROM #TimesheetLineData'

SET @sql3=@sql3 + ') AS SourceTable
    PIVOT
    (
        MAX(PlannedWork)'
SET @sql4 = ' FOR TimeByDay IN ('+ @TimeByDay +')
    ) AS PivotTable
    ) A 
    ORDER BY ResourceName, [Task Name/Description],SOW';

EXEC  (@sql + @sql3 + @sql4)

SET @sql5 = 'INSERT INTO ##fp_FetchTimeSheet_PWA_Data 
    SELECT *,1 As OrderID FROM
        ('

SET @sql7 = ' 
        SELECT ResourceName
            , NULL As SOW               
            , ''Total Actual Work'' As [Task Name/Description]
            , ' + @TimeByDay + '
    FROM
    (
        SELECT 
            ResourceName, 
            SOW,
            [Task Name/Description],
            LEFT(DATENAME(dw, TimeByDay), 3) + '', '' + CAST(TimeByDay AS varchar(11)) AS TimeByDay,
            TotalActualWork
        from #TimesheetLineDataSummary'

set @sql7=@sql7 + ') AS SourceTable
    PIVOT
    (
        MAX(TotalActualWork)'

SET @sql8 = ' FOR TimeByDay IN ('+ @TimeByDay +')
    ) AS PivotTable
    )A
    ORDER BY ResourceName, [Task Name/Description]';

exec  (@sql5 + @sql7 + @sql8)

SELECT *
FROM ##fp_FetchTimeSheet_PWA_Data
ORDER BY ResourceName,OrderID

我有上面的查询，它使用带有动态列的数据透视表作为存储过程的一部分。到目前为止，查询工作正常，但我正在努力为每个返回的行添加一个授权总计列，这将是动态生成的列的总和(在变量 @TimeByDay 中)

我正在尝试关注此处的一篇文章 https://www.codeproject.com/articles/232181/sql-pivot-with-grand-total-column-and-row但有些方法，我似乎无法让它工作。

Answer 1

一般来说:总计是——在大多数情况下! - 表示层应该计算的东西。

如果你必须这样做，你有两个选择:

1. 让您的 SP 返回 SELECT 原样 并再放置一个 SELECT，它只返回包含您所有 SUM 的一行需要。根据您调用它的工具，可以通过一次调用 SP 来读取多个结果集。

2. 使用这样的东西

该代码使用 UNION ALL 来添加您的总计。需要 RowInx 和排序以确保正确的顺序和最后一位的总计。

DECLARE @tbl TABLE (ID INT IDENTITY,val1 INT, val2 INT);
INSERT INTO @tbl VALUES (1,2),(3,4),(5,6);

SELECT ID,val1,val2
FROM
(
    SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowInx, * FROM @tbl
    UNION ALL
    SELECT 1000000,NULL,SUM(val1),SUM(val2) FROM @tbl
) AS t
ORDER BY t.RowInx