Microsoft SQL Server 實(shí)現(xiàn)數(shù)據(jù)透視表

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net



文章目錄

        使用 CASE 表達(dá)式和分組聚合
        使用 PIVOT 運(yùn)算符
        創(chuàng)建動(dòng)態(tài)透視表
        總結(jié)

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。數(shù)據(jù)透視表（Pivot Table）是 Excel 中一個(gè)非常實(shí)用的分析功能，可以用于實(shí)現(xiàn)復(fù)雜的數(shù)據(jù)分類匯總和對(duì)比分析，是數(shù)據(jù)分析師和運(yùn)營人員必備技能之一。前文已經(jīng)介紹了 MySQL/MariaDB 和 Oracle 中的數(shù)據(jù)透視表實(shí)現(xiàn)，今天我們來談?wù)勅绾卧?Microsoft SQL Server 中實(shí)現(xiàn)相同的功能。

    ??本文使用的示例數(shù)據(jù)可以點(diǎn)此下載。

使用 CASE 表達(dá)式和分組聚合

實(shí)現(xiàn)數(shù)據(jù)行轉(zhuǎn)列的一個(gè)通用方法就是利用 CASE 條件表達(dá)式和分組聚合操作。首先使用以下 GROUP BY 子句對(duì)銷售數(shù)據(jù)進(jìn)行分類匯總：

select coalesce(product, '【全部產(chǎn)品】') "產(chǎn)品",
       coalesce(channel, '【所有渠道】') "渠道",
       coalesce(format(saledate, 'yyyyMM'), '【所有月份】') "月份",
       sum(amount) "銷量"
from sales_data
group by rollup (product,channel,format(saledate, 'yyyyMM'));

 

以上語句按照產(chǎn)品、渠道以及月份進(jìn)行匯總；rollup 選項(xiàng)用于生成不同層次的小計(jì)、合計(jì)以及總計(jì)；coalesce 函數(shù)用于將匯總行中的 NULL 值顯示為相應(yīng)的信息。該查詢返回的結(jié)果如下：

產(chǎn)品      |渠道      |月份       |銷量    |
---------|---------|-----------|-------|
桔子      |店面      |201901    |  41306|
桔子      |店面      |201902    |  37906|
桔子      |店面      |201903    |  48866|
桔子      |店面      |201904    |  48673|
桔子      |店面      |201905    |  58998|
桔子      |店面      |201906    |  58931|
桔子      |店面      |【所有月份】| 294680|
桔子      |京東      |201901    |  41289|
桔子      |京東      |201902    |  43913|
桔子      |京東      |201903    |  49803|
桔子      |京東      |201904    |  49256|
桔子      |京東      |201905    |  64889|
桔子      |京東      |201906    |  62649|
桔子      |京東      |【所有月份】| 311799|
桔子      |淘寶      |201901    |  43488|
桔子      |淘寶      |201902    |  37598|
桔子      |淘寶      |201903    |  48621|
桔子      |淘寶      |201904    |  49919|
桔子      |淘寶      |201905    |  58530|
桔子      |淘寶      |201906    |  64626|
桔子      |淘寶      |【所有月份】| 302782|
桔子      |【所有渠道】|【所有月份】| 909261|
...
香蕉      |【所有渠道】|【所有月份】| 925369|
【全部產(chǎn)品】|【所有渠道】|【所有月份】|2771682|

 

接下來我們將數(shù)據(jù)按照不同月份顯示為不同的列，也就是將行轉(zhuǎn)換為列，這個(gè)功能可以使用 CASE 表達(dá)式實(shí)現(xiàn)：

select coalesce(product, '【全部產(chǎn)品】') "產(chǎn)品", coalesce(channel, '【所有渠道】') "渠道",
       sum(case format(saledate, 'yyyyMM') when '201901' then amount else 0 end) "一月",
       sum(case format(saledate, 'yyyyMM') when '201902' then amount else 0 end) "二月",
       sum(case format(saledate, 'yyyyMM') when '201903' then amount else 0 end) "三月",
       sum(case format(saledate, 'yyyyMM') when '201904' then amount else 0 end) "四月",
       sum(case format(saledate, 'yyyyMM') when '201905' then amount else 0 end) "五月",
       sum(case format(saledate, 'yyyyMM') when '201906' then amount else 0 end) "六月",
       sum(amount) "總計(jì)"
from sales_data
group by rollup (product, channel);

產(chǎn)品       |渠道       |一月  |二月   |三月   |四月   |五月  |六月   |總計(jì)   |
----------|----------|------|------|------|------|------|------|-------|
桔子       |店面      | 41306| 37906| 48866| 48673| 58998| 58931| 294680|
桔子       |京東      | 41289| 43913| 49803| 49256| 64889| 62649| 311799|
桔子       |淘寶      | 43488| 37598| 48621| 49919| 58530| 64626| 302782|
桔子       |【所有渠道】|126083|119417|147290|147848|182417|186206| 909261|
蘋果       |店面      | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
蘋果       |京東      | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
蘋果       |淘寶      | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
蘋果       |【所有渠道】|125083|124421|150230|170360|180136|186822| 937052|
香蕉       |店面      | 41210| 39420| 50884| 52085| 60249| 67597| 311445|
香蕉       |京東      | 36879| 36981| 51748| 54801| 64936| 60688| 306033|
香蕉       |淘寶      | 42468| 41955| 52780| 54971| 56504| 59213| 307891|
香蕉       |【所有渠道】|120557|118356|155412|161857|181689|187498| 925369|
【全部產(chǎn)品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|

 

第一個(gè) SUM 函數(shù)中的 CASE 表達(dá)式只匯總 201901 月份的銷量，其他月份銷量設(shè)置為 0；后面的 SUM 函數(shù)依次類推，得到了每個(gè)月的銷量匯總和所有月份的總計(jì)。

    ??使用 CASE 條件表達(dá)式和分組聚合的方法也適用于其他數(shù)據(jù)庫。

使用 PIVOT 運(yùn)算符

Microsoft SQL Server 為數(shù)據(jù)的行列轉(zhuǎn)換提供了專用的 PIVOT 和 UNPIVOT 運(yùn)算符。其中，PIVOT 用于將行轉(zhuǎn)化為列。例如：

with d(saledate, product, channel, amount) as (
  select format(saledate, 'yyyyMM'),
         product,
         channel,
         amount
  from sales_data
)
select *
from d
pivot (
  sum(amount)
  for saledate
  in ([201901], [201902], [201903], [201904], [201905], [201906])
) as pt
order by product;

 

首先通過 with 子句對(duì)數(shù)據(jù)進(jìn)行初步處理；然后利用 pivot 進(jìn)行行轉(zhuǎn)列，包括 3 個(gè)部分；sum(amount) 是需要匯總的數(shù)據(jù)，for saledate 指定了需要轉(zhuǎn)換成多個(gè)列的字段，in 列出了轉(zhuǎn)換為列的數(shù)據(jù)值和轉(zhuǎn)換之后的字段名。

PRODUCT  |CHANNEL |'201901'|'201902'|'201903'|'201904'|'201905'|'201906'|
---------|--------|--------|--------|--------|--------|--------|--------|
桔子     |店面     |   41306|   37906|   48866|   48673|   58998|   58931|
桔子     |京東     |   41289|   43913|   49803|   49256|   64889|   62649|
桔子     |淘寶     |   43488|   37598|   48621|   49919|   58530|   64626|
蘋果     |店面     |   43845|   40539|   44909|   55646|   56771|   64933|
蘋果     |京東     |   38269|   40593|   56552|   56662|   64493|   62045|
蘋果     |淘寶     |   42969|   43289|   48769|   58052|   58872|   59844|
香蕉     |店面     |   41210|   39420|   50884|   52085|   60249|   67597|
香蕉     |京東     |   36879|   36981|   51748|   54801|   64936|   60688|
香蕉     |淘寶     |   42468|   41955|   52780|   54971|   56504|   59213|

 

接下來我們還需要增加一個(gè)總計(jì)行和總計(jì)列，為此可以先將 sales_data 數(shù)據(jù)進(jìn)行分組統(tǒng)計(jì)然后再使用 PIVOT 進(jìn)行轉(zhuǎn)換：

with d(saledate, product, channel, amount) as (
  select format(saledate, 'yyyyMM'),
         product,
         channel,
         sum(amount)
  from sales_data
  group by rollup (format(saledate, 'yyyyMM'),product,channel)
)
select coalesce(product, '【全部產(chǎn)品】') "產(chǎn)品",
       coalesce(channel, '【所有渠道】') "渠道",
       [201901] "一月", [201902] "二月", [201903] "三月",
       [201904] "四月", [201905] "五月", [201906] "六月",
       [201901]+[201902]+[201903]+[201904]+[201905]+[201906] "總計(jì)"
from d
pivot (
  sum(amount)
  for saledate
  in ([201901], [201902], [201903], [201904], [201905], [201906])
) as pt
order by product desc, channel desc;

 

我們通過增加一些總計(jì)數(shù)據(jù)并且修改了返回字段的名稱，讓結(jié)果更加接近 EXCEL 數(shù)據(jù)透視表：

產(chǎn)品       |渠道       |一月  |二月   |三月   |四月   |五月  |六月   |總計(jì)   |
----------|----------|------|------|------|------|------|------|-------|
香蕉       |淘寶      | 42468| 41955| 52780| 54971| 56504| 59213| 307891|
香蕉       |京東      | 36879| 36981| 51748| 54801| 64936| 60688| 306033|
香蕉       |店面      | 41210| 39420| 50884| 52085| 60249| 67597| 311445|
香蕉       |【所有渠道】|120557|118356|155412|161857|181689|187498| 925369|
蘋果       |淘寶      | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
蘋果       |京東      | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
蘋果       |店面      | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
蘋果       |【所有渠道】|125083|124421|150230|170360|180136|186822| 937052|
桔子       |淘寶      | 43488| 37598| 48621| 49919| 58530| 64626| 302782|
桔子       |京東      | 41289| 43913| 49803| 49256| 64889| 62649| 311799|
桔子       |店面      | 41306| 37906| 48866| 48673| 58998| 58931| 294680|
桔子       |【所有渠道】|126083|119417|147290|147848|182417|186206| 909261|
【全部產(chǎn)品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|

 

與 PIVOT 相反的操作是 UNPIVOT，它可以將列轉(zhuǎn)換為行。我們通過以下示例將行轉(zhuǎn)列之后的數(shù)據(jù)再轉(zhuǎn)換回來：

with d(saledate, product, channel, amount) as (
  select format(saledate, 'yyyyMM'),
         product,
         channel,
         amount
  from sales_data
)
select product, channel, saledate, amount
from (
  select *
  from d
  pivot (
    sum(amount)
    for saledate
    in ([201901], [201902], [201903], [201904], [201905], [201906])
  ) as pt
) as t
unpivot (
  amount
  for saledate
  IN ([201901], [201902], [201903], [201904], [201905], [201906])
) as upt;

 

其中，unpivot 操作符也有三個(gè)選項(xiàng)，用于將每個(gè)月份對(duì)應(yīng)的字段轉(zhuǎn)換為 saledate 字段中的行，并且將對(duì)應(yīng)的數(shù)據(jù)轉(zhuǎn)換為 amount 字段中的行。以上查詢返回的結(jié)果如下：

product |channel |saledate |amount  |
--------|--------|---------|--------|
桔子     |店面     |201901  |41306.00|
桔子     |店面     |201902  |37906.00|
桔子     |店面     |201903  |48866.00|
桔子     |店面     |201904  |48673.00|
桔子     |店面     |201905  |58998.00|
桔子     |店面     |201906  |58931.00|
蘋果     |店面     |201901  |43845.00|
蘋果     |店面     |201902  |40539.00|
蘋果     |店面     |201903  |44909.00|
蘋果     |店面     |201904  |55646.00|
蘋果     |店面     |201905  |56771.00|
蘋果     |店面     |201906  |64933.00|
...

 

PIVOT 和 UNPIVOT 操作符的語法如下：

SELECT *
FROM table_source
PIVOT (  
    aggregate_function(aggregated_column)
    FOR pivot_column
    IN ( [column_name], ...)
) AS alias;

SELECT *
FROM table_source
UNPIVOT (
    value_column
    FOR pivot_column
    IN ( [column_name], ...)
) AS alias;

 

    ??如果想要解鎖更多的 PIVOT 和 UNPIVOT 的使用姿勢，可以參考官方文檔中的語法和示例。

創(chuàng)建動(dòng)態(tài)透視表

對(duì)于以上兩種方法，如果 sales_data 中增加了 7 月份到 12 月份的銷量，我們就需要修改查詢語句增加這部分的處理。為了方便處理，我們可以使用動(dòng)態(tài) SQL 生成動(dòng)態(tài)透視表：

declare
  @columns  nvarchar(max) = '',
  @tot_col  nvarchar(max) = '',
  @query    nvarchar(max) = '';
 
-- 生成需要轉(zhuǎn)換為列的字段名
select @columns += quotename(saledate) + ',',
       @tot_col += quotename(saledate) + '+'
from (
  select distinct format(saledate, 'yyyyMM') as saledate
  from sales_data
  ) d
order by saledate;
 
-- 刪除字符串結(jié)尾處的逗號(hào)
set @columns = left(@columns, len(@columns) - 1);
set @tot_col = left(@tot_col, len(@tot_col) - 1);
 
-- 創(chuàng)建動(dòng)態(tài) SQL 語句
set @query ='
with d(saledate, product, channel, amount) as (
  select format(saledate, ''yyyyMM''),
         product,
         channel,
         sum(amount)
  from sales_data
  group by rollup (format(saledate, ''yyyyMM''),product,channel)
)
select coalesce(product, ''ALL'') product,
       coalesce(channel, ''ALL'') channel,
       '+ @columns + ',
       '+ @tot_col +' as total
from d
pivot(
  sum(amount)
  for saledate
  in ('+ @columns +')
) as pt
order by product desc, channel desc;';

print @query;

-- 執(zhí)行動(dòng)態(tài)語句
execute sp_executesql @query;

 

首先，通過查詢 sales_data 得到轉(zhuǎn)換之后的字段名和總計(jì)字段；然后，拼接出查詢語句 @query；print 語句打印出的查詢語句如下：

with d(saledate, product, channel, amount) as (
  select format(saledate, 'yyyyMM'),
         product,
         channel,
         sum(amount)
  from sales_data
  group by rollup (format(saledate, 'yyyyMM'),product,channel)
)
select coalesce(product, 'ALL') product,
       coalesce(channel, 'ALL') channel,
       [201901],[201902],[201903],[201904],[201905],[201906],
       [201901]+[201902]+[201903]+[201904]+[201905]+[201906] as total
from d
pivot(
  sum(amount)
  for saledate
  in ([201901],[201902],[201903],[201904],[201905],[201906])
) as pt
order by product desc, channel desc;

 

最后，利用 sp_executesql 存儲(chǔ)過程執(zhí)行動(dòng)態(tài)查詢返回?cái)?shù)據(jù)透視表。
總結(jié)

數(shù)據(jù)透視表是進(jìn)行數(shù)據(jù)匯總、分析、瀏覽和展示的強(qiáng)大工具，可以幫助我們了解數(shù)據(jù)中的對(duì)比情況、模式和趨勢。本文介紹了在 Microsoft SQL Server 中實(shí)現(xiàn)數(shù)據(jù)透視表的三種方式，包括 CASE 條件表達(dá)式和分組聚合操作相結(jié)合、專用的 PIVOT 運(yùn)算符以及使用動(dòng)態(tài) SQL 語句生成動(dòng)態(tài)數(shù)據(jù)透視表。

如果覺得文章對(duì)你有用，歡迎關(guān)注??、評(píng)論??、點(diǎn)贊??！