如何在SQL Server中的数据透视图中的聚合函数上使用case语句 [英] How to use case statement over aggregrate function in pivot in SQL Server

问题描述

您好，

 我在需要生成报表的交叉表结果中使用数据透视表.我在数据透视查询中使用了Count聚合函数.因此，如果Count为大于0，那么我需要打印为是"，否则为否".

 I am using pivot for cross tab results in which i need to generate the report.Am using the Count aggregate function in the Pivot query.So if the Count is greater than 0 then i need to print as ''Yes'' otherwise ''No''.

任何人都可以帮助我如何在Pivot中的聚合函数上使用case语句

Can anyone please help me how to use case statement over aggregate function in Pivot

Shameer Khan 

Shameer Khan 

推荐答案

，也许您可​​以张贴到目前为止已编写的代码.它将帮助我们提供最后一步.

Perhaps you could post the code you have written so far. It would help us provide the last step.

您不能在枢轴中将case语句与聚合函数一起使用.

请参考以下URL:-http://technet.microsoft.com/zh-cn/library/ms177410.aspx

如果要使用case语句，则可以在使用了ivot关键字的实际select语句中使用.检查以下语法:-
SELECT``Sun''=
如果[Sun]> 0则为"true"，否则为"false"结束，
``星期一''=
[Mon]> 0时为"true"，否则为"false"的情况，
``Tue''=
[Tue]> 0时为"true"，否则为"false"结束的情况

来自
(选择
天数，费用
来自
产品
)P
PIVOT(
总和(费用)

FOR
天数
(
[Sun]，
[星期一]，
[Tue]
))AS PVT

这样，您可以在查询中使用case语句. :)

谢谢

Mehul Thakkar

Hi,

you can not use case statement along with aggregate function in pivot.

refer the following URL :- http://technet.microsoft.com/en-us/library/ms177410.aspx

If you want to use case statement then you can use in actual select statement in which you have used pivot keyword. check the following syntax:-
SELECT ''Sun''=
case when [Sun]>0 then ''true'' else ''false'' end,
''Mon'' =
case when [Mon]>0 then ''true'' else ''false'' end,
''Tue'' =
case when [Tue]>0 then ''true'' else ''false'' end

FROM
(Select
Days, Cost
From
Product
)P
PIVOT(
Sum(Cost)

FOR
Days IN
(
[Sun],
[Mon],
[Tue]
)) AS PVT

This way you can use case statement in your query. :)

Thanks

Mehul Thakkar

这篇关于如何在SQL Server中的数据透视图中的聚合函数上使用case语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！