SQL 2008 R2 中的数据透视 [英] Pivot in SQL 2008 R2
本文介绍了SQL 2008 R2 中的数据透视的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这样的桌子;
Date PlacementName campaignID Impressions Clicks TotalConversions Activity
01/01/2014 USA 100 5000 500 50 Mobile Book
01/02/2014 U.K 101 7000 250 30 Mobile Book
01/01/2014 USA 100 9000 800 40 Mobile TV
01/02/2014 U.K 101 6000 300 10 Mobile TV
我想从真实表中透视 15-20 Activity 的表,因为这只是示例表.
I want to pivot table for 15-20 Activity from the real table because this is just example table.
我希望我的表格如下所示;
I want my table look like below;
Date PlacementName CampaignID Impressions Clicks Mobile Book Mobile TV
01/01/2014 USA 100 5000 500 50 NULL
01/01/2014 U.K 100 9000 800 NULL 40
01/02/2014 USA 101 7000 250 30 NULL
01/02/2014 U.K 101 6000 300 NULL 10
这是我在决赛桌中想要的格式.我想为这个表写查询.
This is the format I want in final table. I want to write query for this table.
推荐答案
获得结果的最简单方法是使用聚合函数和 CASE 表达式将数据行转换为列,但您也可以应用PIVOT 函数.
The easiest way to get the result would be to use an aggregate function along with a CASE expression to convert the rows of data into columns, but you could also apply the PIVOT function.
select date,
placementname,
campaignid,
impressions,
clicks,
sum(case when activity = 'Mobile Book' then TotalConversions else 0 end) MobileBook,
sum(case when activity = 'Mobile TV' then TotalConversions else 0 end) MobileTV
from yourtable
group by date, placementname, campaignid, impressions, clicks;
select date, placementname,
campaignid, impressions,
clicks,
[Mobile Book], [Mobile TV]
from
(
select date, placementname,
campaignid, impressions,
clicks, activity, totalconversions
from yourtable
) d
pivot
(
sum(totalconversions)
for activity in ([Mobile Book], [Mobile TV])
) p
这篇关于SQL 2008 R2 中的数据透视的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文