我有一组输出数据透视表的查询.是否可以获得数据透视表的行和/或列小计?
@H_403_2@我选择的桌子看起来像这样
Site FormID Present Site 1 Form A Yes Site 1 Form B Yes Site 1 Form D Yes@H_403_2@等等… @H_403_2@我的数据透视表查询是这样的
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;@H_403_2@但我真的希望它能够产生这种结果(当然这对我来说并不完全)
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@H_403_2@感谢你的协助 ! @H_403_2@-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@H_403_2@注意:如果您使用的是sql Server 2005,则需要更改此设置:
(SELECT SUM(S) FROM (VALUES([Site 1]),([Site 3])) AS T(S)) as Total@H_403_2@至
(SELECT SUM(S) FROM (SELECT [Site 1] UNION ALL SELECT [Site 2] UNION ALL SELECT [Site 3]) AS T(S)) as Total@H_403_2@试试SE Data