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
上面是我的演示数据库,其中包含gument_usd,paytype和senditemdateTime1列。当我在下面的查询中使用Pivok时,它在下面出现结果,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
以下是上面查询的结果。
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
及以下是我想要的...
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
对不起,第一次发布问题...
编辑
这项为我的工作" 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;
您可以在第一个表中使用以下查询旋转数据 - 您只需要明确列出所有付款类型列即可。我认为SUM()
是要应用的聚合:
SELECT SendItemDateTime1, [google],[transfer],[paypal],[molpay]
FROM MyTable
PIVOT
(
SUM(amount_usd)
for [paytype] IN ([google],[transfer],[paypal],[molpay])
) piv;
sqlfiddle
编辑,RE我在哪里过滤
如果可以将过滤器谓词应用于最终列,则可以在枢轴之后应用WHERE
。否则,如果需要在未分类的列中进行过滤,则可以按照您的操作来使用CTE或派生表。这是CTE和PostFilter中的prefelter的示例:
-- 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';
更新了小提琴