加入收藏 | 设为首页 | 会员中心 | 我要投稿 济源站长网 (https://www.0391zz.cn/)- 数据工具、数据仓库、行业智能、CDN、运营!
当前位置: 首页 > 站长学院 > MsSql教程 > 正文

sql-server – 您可以在数据透视表中小计行和/或列吗?

发布时间:2021-01-23 03:06:28 所属栏目:MsSql教程 来源:网络整理
导读:我有一组输出数据透视表的查询.是否可以获得数据透视表的行和/或列小计? 我选择的桌子看起来像这样 Site FormID Present Site 1 Form A Yes Site 1 Form B Yes Site 1 Form D Yes 等等… 我的数据透视表查询是这样的 SELECT * FROM (SELECT Site,COUNT(FormI

我有一组输出数据透视表的查询.是否可以获得数据透视表的行和/或列小计?

我选择的桌子看起来像这样

Site     FormID   Present
    Site 1   Form A      Yes
    Site 1   Form B      Yes
    Site 1   Form D      Yes

等等…

我的数据透视表查询是这样的

SELECT *
   FROM (SELECT Site,COUNT(FormID) AS NumberOfForms,FormID
         FROM @CRFCount WHERE Present='Yes'
         GROUP BY Site,FormID) d
   PIVOT
   (SUM(NumberOfForms)
   FOR [Site] IN ([Site 1],[Site 2],[Site 3])
   )  AS p;

但我真的希望它能够产生这种结果(当然这对我来说并不完全)

FormID  Site 1  Site 2  Site 3  Total
    Form A      8      8      15    31
    Form B     14      4    NULL    18
    Form C     14   NULL    NULL    14
    Form D     15      3      16    34
    Form E     12      4    NULL    16
    Form F     14      5       5    24
    Form G     14      8       6    28
    Form H     22     10      15    47
    Form I     15     10      16    41
    Form J     15      5      16    36
    Total     143     57      89   289

感谢你的协助 !

-Don

解决方法

;WITH C as
(
  SELECT FormID,[Site 1],[Site 3],(SELECT SUM(S)
          FROM (VALUES([Site 1]),([Site 2]),([Site 3])) AS T(S)) as Total
   FROM (SELECT Site,[Site 3])
   )  AS p
)
SELECT *
FROM
  (
    SELECT FormID,Total
    FROM C
    UNION ALL
    SELECT 'Total',SUM([Site 1]),SUM([Site 2]),SUM([Site 3]),SUM(Total)
    FROM C
  ) AS T
ORDER BY CASE WHEN FormID = 'Total' THEN 1 END

注意:如果您使用的是SQL Server 2005,则需要更改此设置:

(SELECT SUM(S)
  FROM (VALUES([Site 1]),([Site 3])) AS T(S)) as Total

(SELECT SUM(S)
  FROM (SELECT [Site 1] UNION ALL
        SELECT [Site 2] UNION ALL
        SELECT [Site 3]) AS T(S)) as Total

试试SE Data

(编辑:济源站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    热点阅读