我有一组输出数据透视表的查询.是否可以获得数据透视表的行和/或列小计？
我选择的桌子看起来像这样
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
