深入SQL中PIVOT 行列转换详解
<p>PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT与PIVOT执行相反的操作，将表值表达式的列转换为列值。</p>
<p>
通俗简单的说：PIVOT就是行转列，UNPIVOT就是列传行</p>
<p>
<span><strong>一、PIVOT实例</strong></span></p>
<p>
<strong>1. 建表</strong></p>
<p>
建立一个销售情况表，其中，year字段表示年份，quarter字段表示季度，amount字段表示销售额。quarter字段分别用Q1, Q2, Q3, Q4表示一、二、三、四季度。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_802224">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">CREATE</code> <code class="sql keyword">TABLE</code> <code class="sql plain">SalesByQuarter</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">( </code><code class="sql color2">year</code> <code class="sql keyword">INT</code><code class="sql plain">, </code><code class="sql comments">-- 年份</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql plain">quarter </code><code class="sql keyword">CHAR</code><code class="sql plain">(2), </code><code class="sql comments">-- 季度</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">amount MONEY </code><code class="sql comments">-- 总额</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<strong>2. 填入表数据</strong></p>
<p>
使用如下程序填入表数据。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_794360">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SET</code> <code class="sql plain">NOCOUNT </code><code class="sql keyword">ON</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql spaces"> </code><code class="sql keyword">DECLARE</code> <code class="sql plain">@</code><code class="sql keyword">index</code> <code class="sql keyword">INT</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql keyword">DECLARE</code> <code class="sql plain">@q </code><code class="sql keyword">INT</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql keyword">SET</code> <code class="sql plain">@</code><code class="sql keyword">index</code> <code class="sql plain">= 0</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql keyword">DECLARE</code> <code class="sql plain">@</code><code class="sql color2">year</code> <code class="sql keyword">INT</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code><code class="sql plain">while (@</code><code class="sql keyword">index</code> <code class="sql plain">< 30)</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql spaces"> </code><code class="sql keyword">BEGIN</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql spaces"> </code><code class="sql keyword">SET</code> <code class="sql plain">@</code><code class="sql color2">year</code> <code class="sql plain">= 2005 + (@</code><code class="sql keyword">index</code> <code class="sql plain">% 4)</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql spaces"> </code><code class="sql keyword">SET</code> <code class="sql plain">@q = (</code><code class="sql color2">CAST</code><code class="sql plain">((RAND() * 500) </code><code class="sql keyword">AS</code> <code class="sql keyword">INT</code><code class="sql plain">) % 4) + 1</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql spaces"> </code><code class="sql keyword">INSERT</code> <code class="sql keyword">INTO</code> <code class="sql plain">SalesByQuarter </code><code class="sql keyword">VALUES</code> <code class="sql plain">(@</code><code class="sql color2">year</code><code class="sql plain">, </code><code class="sql string">'Q'</code> <code class="sql plain">+ </code><code class="sql color2">CAST</code><code class="sql plain">(@q </code><code class="sql keyword">AS</code> <code class="sql keyword">CHAR</code><code class="sql plain">(1)), RAND() * 10000.00)</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql spaces"> </code><code class="sql keyword">SET</code> <code class="sql plain">@</code><code class="sql keyword">index</code> <code class="sql plain">= @</code><code class="sql keyword">index</code> <code class="sql plain">+ 1</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<strong>3、如果我们要比较每年中各季度的销售状况，要怎么办呢？有以下两种方法：</strong></p>
<p>
<strong>（1）、使用传统Select的CASE语句查询</strong></p>
<p>
在SQL Server以前的版本里，将行级数据转换为列级数据就要用到一系列CASE语句和聚合查询。虽然这种方式让开发人员具有了对所返回数据进行高度控制的能力，但是编写出这些查询是一件很麻烦的事情。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_98504">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SELECT</code> <code class="sql color2">year</code> <code class="sql keyword">as</code> <code class="sql plain">年份</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql spaces"> </code><code class="sql plain">, </code><code class="sql color2">sum</code> <code class="sql plain">(</code><code class="sql color2">case</code> <code class="sql keyword">when</code> <code class="sql plain">quarter = </code><code class="sql string">'Q1'</code> <code class="sql keyword">then</code> <code class="sql plain">amount </code><code class="sql keyword">else</code> <code class="sql plain">0 </code><code class="sql keyword">end</code><code class="sql plain">) 一季度</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql plain">, </code><code class="sql color2">sum</code> <code class="sql plain">(</code><code class="sql color2">case</code> <code class="sql keyword">when</code> <code class="sql plain">quarter = </code><code class="sql string">'Q2'</code> <code class="sql keyword">then</code> <code class="sql plain">amount </code><code class="sql keyword">else</code> <code class="sql plain">0 </code><code class="sql keyword">end</code><code class="sql plain">) 二季度</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">, </code><code class="sql color2">sum</code> <code class="sql plain">(</code><code class="sql color2">case</code> <code class="sql keyword">when</code> <code class="sql plain">quarter = </code><code class="sql string">'Q3'</code> <code class="sql keyword">then</code> <code class="sql plain">amount </code><code class="sql keyword">else</code> <code class="sql plain">0 </code><code class="sql keyword">end</code><code class="sql plain">) 三季度</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql plain">, </code><code class="sql color2">sum</code> <code class="sql plain">(</code><code class="sql color2">case</code> <code class="sql keyword">when</code> <code class="sql plain">quarter = </code><code class="sql string">'Q4'</code> <code class="sql keyword">then</code> <code class="sql plain">amount </code><code class="sql keyword">else</code> <code class="sql plain">0 </code><code class="sql keyword">end</code><code class="sql plain">) 四季度</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql keyword">FROM</code> <code class="sql plain">SalesByQuarter </code><code class="sql keyword">GROUP</code> <code class="sql keyword">BY</code> <code class="sql color2">year</code> <code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql color2">year</code> <code class="sql keyword">DESC</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
得到的结果如下：</p>
<p>
<img title="深入SQL中PIVOT 行列转换详解" alt="深入SQL中PIVOT 行列转换详解" src="https://zhuji.jb51.net/uploads/img/202305/22c2870032cc81d02e0e34f28b33089c.jpg"></p>
<p>
<strong>（2）、使用PIVOT</strong></p>
<p>
由于SQL Server 2005有了新的PIVOT运算符，就不再需要CASE语句和GROUP BY语句了。（每个PIVOT查询都涉及某种类型的聚合，因此你可以忽略GROUP BY语句。）PIVOT运算符让我们能够利用CASE语句查询实现相同的功能，但是你可以用更少的代码就实现，而且看起来更漂亮。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_14825">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SELECT</code> <code class="sql color2">year</code> <code class="sql keyword">as</code> <code class="sql plain">年份, Q1 </code><code class="sql keyword">as</code> <code class="sql plain">一季度, Q2 </code><code class="sql keyword">as</code> <code class="sql plain">二季度, Q3 </code><code class="sql keyword">as</code> <code class="sql plain">三季度, Q4 </code><code class="sql keyword">as</code> <code class="sql plain">四季度 </code><code class="sql keyword">FROM</code> <code class="sql plain">SalesByQuarter PIVOT (</code><code class="sql color2">SUM</code> <code class="sql plain">(amount) </code><code class="sql keyword">FOR</code> <code class="sql plain">quarter </code><code class="sql color1">IN</code> <code class="sql plain">(Q1, Q2, Q3, Q4) ) </code><code class="sql keyword">AS</code> <code class="sql plain">P </code><code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql color2">YEAR</code> <code class="sql keyword">DESC</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
得到的结果如下：</p>
<p>
<img title="深入SQL中PIVOT 行列转换详解" alt="深入SQL中PIVOT 行列转换详解" src="https://zhuji.jb51.net/uploads/img/202305/3f123949446e71fb50cd2247fc7b6d46.jpg"></p>
<p>
<span><strong>二、通过下面一个实例详细介绍PIVOT的过程</strong></span></p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_40414">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SELECT</code> <code class="sql plain">[星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]</code><code class="sql comments">--这里是PIVOT第三步（选择行转列后的结果集的列）这里可以用“*”表示选择所有列，也可以只选择某些列(也就是某些天)</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">FROM</code> <code class="sql plain">WEEK_INCOME </code><code class="sql comments">--这里是PIVOT第二步骤(准备原始的查询结果，因为PIVOT是对一个原始的查询结果集进行转换操作，所以先查询一个结果集出来)这里可以是一个select子查询，但为子查询时候要指定别名，否则语法错误</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">PIVOT</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql plain">(</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql color2">SUM</code><code class="sql plain">(INCOME) </code><code class="sql keyword">for</code> <code class="sql plain"> </code><code class="sql color1">in</code><code class="sql plain">([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])</code><code class="sql comments">--这里是PIVOT第一步骤，也是核心的地方，进行行转列操作。聚合函数SUM表示你需要怎样处理转换后的列的值，是总和(sum)，还是平均(avg)还是min,max等等。例如如果week_income表中有两条数据并且其week都是“星期一”，其中一条的income是1000,另一条income是500，那么在这里使用sum，行转列后“星期一”这个列的值当然是1500了。后面的for in([星期一],[星期二]...)中 for 就是说将week列的值分别转换成一个个列，也就是“以值变列”。但是需要转换成列的值有可能有很多，我们只想取其中几个值转换成列，那么怎样取呢？就是在in里面了，比如我此刻只想看工作日的收入，在in里面就只写“星期一”至“星期五”（注意，in里面是原来week列的值,"以值变列"）。总的来说，SUM(INCOME) for in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])这句的意思如果直译出来，就是说：将列值为"星期一","星期二","星期三","星期四","星期五","星期六","星期日"分别转换成列，这些列的值取income的总和。</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql plain">)TBL</code><code class="sql comments">--别名一定要写</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<span><strong>三.UNPIVOT</strong></span></p>
<p>
很明显，UN这个前缀表明了，它做的操作是跟PIVOT相反的，即列转行。UNPIVOT操作涉及到以下三个逻辑处理阶段。</p>
<p>
1，生成副本<br>
2，提取元素<br>
3，删除带有NULL的行</p>
<p>
<strong>UNPIVOT实例</strong></p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_803260">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">CREATE</code> <code class="sql keyword">TABLE</code> <code class="sql plain">pvt (VendorID </code><code class="sql keyword">int</code><code class="sql plain">, Emp1 </code><code class="sql keyword">int</code><code class="sql plain">, Emp2 </code><code class="sql keyword">int</code><code class="sql plain">,</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql spaces"> </code><code class="sql plain">Emp3 </code><code class="sql keyword">int</code><code class="sql plain">, Emp4 </code><code class="sql keyword">int</code><code class="sql plain">, Emp5 </code><code class="sql keyword">int</code><code class="sql plain">);</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">GO</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql keyword">INSERT</code> <code class="sql keyword">INTO</code> <code class="sql plain">pvt </code><code class="sql keyword">VALUES</code> <code class="sql plain">(1,4,3,5,4,4);</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql keyword">INSERT</code> <code class="sql keyword">INTO</code> <code class="sql plain">pvt </code><code class="sql keyword">VALUES</code> <code class="sql plain">(2,4,1,5,5,5);</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql keyword">INSERT</code> <code class="sql keyword">INTO</code> <code class="sql plain">pvt </code><code class="sql keyword">VALUES</code> <code class="sql plain">(3,4,3,5,4,4);</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql keyword">INSERT</code> <code class="sql keyword">INTO</code> <code class="sql plain">pvt </code><code class="sql keyword">VALUES</code> <code class="sql plain">(4,4,2,5,5,4);</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql keyword">INSERT</code> <code class="sql keyword">INTO</code> <code class="sql plain">pvt </code><code class="sql keyword">VALUES</code> <code class="sql plain">(5,5,1,5,5,5);</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql plain">GO</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql comments">--Unpivot the table.</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql keyword">SELECT</code> <code class="sql plain">VendorID, Employee, Orders</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql keyword">FROM</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql spaces"> </code><code class="sql plain">(</code><code class="sql keyword">SELECT</code> <code class="sql plain">VendorID, Emp1, Emp2, Emp3, Emp4, Emp5</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql spaces"> </code><code class="sql keyword">FROM</code> <code class="sql plain">pvt) p</code>
</div>
<div class="line number15 index14 alt2">
<code class="sql plain">UNPIVOT</code>
</div>
<div class="line number16 index15 alt1">
<code class="sql spaces"> </code><code class="sql plain">(Orders </code><code class="sql keyword">FOR</code> <code class="sql plain">Employee </code><code class="sql color1">IN</code>
</div>
<div class="line number17 index16 alt2">
<code class="sql spaces"> </code><code class="sql plain">(Emp1, Emp2, Emp3, Emp4, Emp5)</code>
</div>
<div class="line number18 index17 alt1">
<code class="sql plain">)</code><code class="sql keyword">AS</code> <code class="sql plain">unpvt;</code>
</div>
<div class="line number19 index18 alt2">
<code class="sql plain">GO</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<strong>上面UNPIVOT实例的分析</strong></p>
<p>
UNPIVOT的输入是左表表达式P，第一步，先为P中的行生成多个副本，在UNPIVOT中出现的每一列，都会生成一个副本。因为这里的IN子句有5个列名称，所以要为每个来源行生成5个副本。结果得到的虚拟表中将新增一个列，用来以字符串格式保存来源列的名称（for和IN之间的，上面例子是 Employee ）。第二步，根据新增的那一列中的值从来源列中提取出与列名对应的行。第三步，删除掉结果列值为null的行，完成这个查询。</p>
頁:
[1]