我在哪里将WHERE子句放在具有日期事务的PIVOT中

amount_usd  paytype SendItemDateTime1
5.00    google  2015-04-01
2.00    google  2015-04-01
5.00    transfer    2015-04-01
15.00   google  2015-04-01
5.00    google  2015-04-01
2.00    google  2015-04-02
60.00   google  2015-04-02
60.00   google  2015-04-02
5.00    google  2015-04-03

Above is my demo database which have amount_usd, paytype, and SendItemDateTime1 column. when i using pivok with query below, it come out result below, which the SendItemDateTime1 is not group by... what is the problem?

上面是我的演示数据库,其中包含amount_usd,paytype和SendItemDateTime1列。当我使用下面的查询使用pivot时,它会在下面显示结果,其中SendItemDateTime1不是由...组成的问题是什么?

 select amount_usd, paytype, SendItemDateTime1 from tblMobile_RequestOrderLog
  where status = 1 and sendstatus = 1 and enable = 1
  and SendItemDateTime1  between '4/1/2015' and '4/30/2015'
  order by SendItemDateTime1

Below is the result from query above.

以下是上述查询的结果。

SenditemDateTime1   google  mol molpay  molstore    paypal  transfer
2015-04-01  15.00   NULL    NULL    NULL    NULL    NULL
2015-04-01  5.00    NULL    NULL    NULL    NULL    NULL
2015-04-01  15.00   NULL    NULL    NULL    NULL    NULL
2015-04-01  5.00    NULL    NULL    NULL    NULL    NULL
2015-04-01  60.00   NULL    NULL    NULL    NULL    NULL
2015-04-01  10.00   NULL    NULL    NULL    NULL    NULL

And below is what i want...

以下是我想要的......

SendItemDate    google  mol molpay  molstore    paypal  transfer
2015-04-01      32      0   0       0          0    5
2015-04-02      122     0   0       0          0    0
2015-04-03      5       0   0       0          0    0

sorry, first time post question here...

对不起,第一次在这里发帖提问...

Edit

This work for me with "Where" clause:

这项工作对我有“Where”条款:

SELECT SendItemDateTime1, COALESCE([google], 0), COALESCE([transfer], 0),
       COALESCE([paypal], 0),COALESCE([molpay], 0)
FROM (Select SendItemDateTime1, paytype, amount_usd 
      from tblMobile_RequestOrderLog
      where gameidn = 248 and status = 1 and sendstatus = 1 and enable = 1 
            and SendItemDateTime1 between '4/1/2015 12:00:00 AM'
                                      and '4/30/2015 11:59:59'
) X 
PIVOT
(
  SUM(amount_usd)
  for [paytype] IN ([google],[transfer],[paypal],[molpay])
) piv;

1 个解决方案

#1

You can pivot the data in your first table with the below query - you'll just need to list all the payment type columns explicitly. I've assumed SUM() as the aggregation to be applied:

您可以使用以下查询来转移第一个表格中的数据 - 您只需要明确列出所有付款类型列。我假设SUM()是要应用的聚合:

SELECT SendItemDateTime1, [google],[transfer],[paypal],[molpay]
FROM MyTable
PIVOT
(
 SUM(amount_usd)
 for [paytype] IN ([google],[transfer],[paypal],[molpay])
) piv;

SqlFiddle here

Edit, Re where do I filter

编辑,重新在哪里过滤

If the filter predicate can be applied to the final columns, then WHERE can be applied after the PIVOT. Otherwise, if the filtering needs to be done in unpivoted columns, then you can use a CTE, or derived table as you have done. Here's an example of prefilter in CTE and postfilter WHERE:

如果过滤谓词可以应用于最终列,则可以在PIVOT之后应用WHERE。否则,如果过滤需要在不透明的列中完成,那么您可以像使用的那样使用CTE或派生表。以下是CTE和后置过滤器WHERE中预过滤器的示例:

-- Prefilter of non-pivot columns done in CTE or Derived table
WITH cte AS
(
  SELECT [amount_usd], [paytype], [SendItemDateTime1]
  FROM MyTable
  WHERE [amount_usd] > 2
)
SELECT SendItemDateTime1, COALESCE([google], 0), COALESCE([transfer], 0),
       COALESCE([paypal], 0),COALESCE([molpay], 0)
FROM cte
PIVOT
(
 SUM(amount_usd)
 for [paytype] IN ([google],[transfer],[paypal],[molpay])
) piv
-- Post filter of pivot columns done on the final projection
WHERE SendItemDateTime1 > '2015-01-01';

Updated Fiddle

#1