SQL 2008 R2中的枢轴 [英] Pivot in 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个活动的表，因为这只是示例表.

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;

请参见带有演示的SQL提琴

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提琴

这篇关于SQL 2008 R2中的枢轴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！