透视表不分组结果如预期
问题描述:
我使用SQL Server 2012.我有两个表,请参见下面透视表不分组结果如预期
Table 1 (Region_Strat)
STRAT SALES_P COMM
EU Bob AC
EU Dave AC
NA John JP
GB Mike AC
Table 2 (Sales_Records)
SALES_P AMT SHOP
Bob 5 ABC
Bob 10 DEF
Bob 3 GHI
Dave 12 ABC
John 3 DEF
Mike 11 LIK
我有一个观点叫vw_sales(存储视图这不能改变),它基本上是增加从Region_Strat表到Sales_Records表的STRAT列向我提供下表。
SALES_P AMT SHOP STRAT
Bob 5 ABC EU
Bob 10 DEF EU
Bob 3 GHI EU
Dave 12 ABC EU
John 3 DEF NA
Mike 11 LIK GB
这很好。接下来,我尝试创建一个交换表(它运行但不会给我我需要的结果)。我的查询是
SELECT SHOP, [EU],[NA],[GB]
INTO MY_DB.dbo.TEMP
FROM
(SELECT SALES.SHOP, cast(SALES.Amt as bigint) AS SumOfAmt, Region.COMM, Region.STRAT
FROM MY_DB.dbo.vw_sales AS SALES JOIN MY_DB.dbo.Region_Strat AS Region ON
SALES.SALES_P = Region.SALES_P
WHERE Region.COMM in ('AC','JP')) tbl
pivot
(sum(SumOfAmt) for STRAT IN([EU],[NA],[GB])) pvt order by SHOP
结果,我希望看到,
SHOP EU NA GB
ABC 17 null null
DEF 10 3 null
GHI 3 null null
LIK null null 11
但是我看到的结果是DEF如下图所示两次店。
SHOP EU NA GB
DEF 10 null null
DEF null 3 null
如何更改我更改我的查询,以便DEF显示在一行上?
答
试试这个:
SELECT SHOP, [EU],[NA],[GB] INTO MY_DB.dbo.TEMP
FROM
(SELECT SALES.SHOP, cast(SALES.Amt as bigint) AS SumOfAmt, Region.STRAT
FROM MY_DB.dbo.vw_sales AS SALES
JOIN MY_DB.dbo.Region_Strat AS Region ON SALES.SALES_P = Region.SALES_P
WHERE Region.COMM in ('AC','JP')
) tbl
pivot (sum(SumOfAmt) for STRAT IN([EU],[NA],[GB])) pvt
order by SHOP
这是工作,我不别名'STRAT_FUND'任何地方螺母它是在'用在Clause' ** ** STRAT_FUND.COMM道歉 – 2014-12-03 12:11:26
- 复制和粘贴错误。它应该是地区,已在我的文章更新。 – mHelpMe 2014-12-03 12:12:44