SQL行转列、列转行的简单实现
<p><span><strong>前言</strong></span></p>
<p>
行列转换在做报表分析时还是经常会遇到的，今天就说一下如何实现行列转换吧。</p>
<p>
行列转换就是如下图所示两种展示形式的互相转换</p>
<p>
<img title="SQL行转列、列转行的简单实现" alt="SQL行转列、列转行的简单实现" src="https://zhuji.jb51.net/uploads/img/202305/7f75bbf6bcc836cdd957e7ae77360c0e.jpg"></p>
<p>
<span><strong>行转列</strong></span></p>
<p>
假如我们有下表：</p>
<p>
<img title="SQL行转列、列转行的简单实现" alt="SQL行转列、列转行的简单实现" src="https://zhuji.jb51.net/uploads/img/202305/0ecbf5c6a8bb698be79e47d316711525.jpg"></p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_436710">
<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">select</code> <code class="sql plain">*</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">from</code> <code class="sql plain">student</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">pivot (</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql color2">sum</code><code class="sql plain">(score) </code><code class="sql keyword">for</code> <code class="sql plain">subject </code><code class="sql color1">in</code> <code class="sql plain">(语文, 数学, 英语)</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
通过上面 sql 语句即可得到下面的结果</p>
<p>
<img title="SQL行转列、列转行的简单实现" alt="SQL行转列、列转行的简单实现" src="https://zhuji.jb51.net/uploads/img/202305/e4267706d8341b3cda89bb989782c043.jpg"></p>
<p>
pivot 后跟一个聚合函数来拿到结果，for 后面跟的科目是我们要转换的列，这样的话科目中的语文、数学、英语就就被转换为列。in 后面跟的就是具体的科目值。</p>
<p>
当然我们也可以用 case when 得到同样的结果，就是写起来麻烦一点。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_102963">
<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>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql keyword">name</code><code class="sql plain">,</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql spaces"> </code><code class="sql keyword">max</code><code class="sql plain">(</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql color2">case</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql keyword">when</code> <code class="sql plain">subject=</code><code class="sql string">'语文'</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql keyword">then</code> <code class="sql plain">score</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code><code class="sql keyword">else</code> <code class="sql plain">0</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql spaces"> </code><code class="sql keyword">end</code><code class="sql plain">) </code><code class="sql keyword">as</code> <code class="sql string">"语文"</code><code class="sql plain">,</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql spaces"> </code><code class="sql keyword">max</code><code class="sql plain">(</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql spaces"> </code><code class="sql color2">case</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql spaces"> </code><code class="sql keyword">when</code> <code class="sql plain">subject=</code><code class="sql string">'数学'</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql spaces"> </code><code class="sql keyword">then</code> <code class="sql plain">score</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql spaces"> </code><code class="sql keyword">else</code> <code class="sql plain">0</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql spaces"> </code><code class="sql keyword">end</code><code class="sql plain">) </code><code class="sql keyword">as</code> <code class="sql string">"数学"</code><code class="sql plain">,</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql spaces"> </code><code class="sql keyword">max</code><code class="sql plain">(</code>
</div>
<div class="line number15 index14 alt2">
<code class="sql spaces"> </code><code class="sql color2">case</code>
</div>
<div class="line number16 index15 alt1">
<code class="sql spaces"> </code><code class="sql keyword">when</code> <code class="sql plain">subject=</code><code class="sql string">'英语'</code>
</div>
<div class="line number17 index16 alt2">
<code class="sql spaces"> </code><code class="sql keyword">then</code> <code class="sql plain">score</code>
</div>
<div class="line number18 index17 alt1">
<code class="sql spaces"> </code><code class="sql keyword">else</code> <code class="sql plain">0</code>
</div>
<div class="line number19 index18 alt2">
<code class="sql spaces"> </code><code class="sql keyword">end</code><code class="sql plain">) </code><code class="sql keyword">as</code> <code class="sql string">"英语"</code>
</div>
<div class="line number20 index19 alt1">
<code class="sql keyword">from</code> <code class="sql plain">student</code>
</div>
<div class="line number21 index20 alt2">
<code class="sql keyword">group</code> <code class="sql keyword">by</code> <code class="sql keyword">name</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
使用 case when 可以得到和 pivot 同样的结果，没有 pivot 简单直观。</p>
<p>
<span><strong>列转行</strong></span></p>
<p>
假设我们有下表 student1</p>
<p>
<img title="SQL行转列、列转行的简单实现" alt="SQL行转列、列转行的简单实现" src="https://zhuji.jb51.net/uploads/img/202305/e4267706d8341b3cda89bb989782c043.jpg"></p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_139810">
<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">select</code> <code class="sql plain">*</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">from</code> <code class="sql plain">student1</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">unpivot (</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">score </code><code class="sql keyword">for</code> <code class="sql plain">subject </code><code class="sql color1">in</code> <code class="sql plain">(</code><code class="sql string">"语文"</code><code class="sql plain">,</code><code class="sql string">"数学"</code><code class="sql plain">,</code><code class="sql string">"英语"</code><code class="sql plain">)</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
通过 unpivot 即可得到如下结果：</p>
<p>
<img title="SQL行转列、列转行的简单实现" alt="SQL行转列、列转行的简单实现" src="https://zhuji.jb51.net/uploads/img/202305/0ecbf5c6a8bb698be79e47d316711525.jpg"></p>
<p>
我们也可以使用下面方法得到同样结果</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_695574">
<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>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql spaces"> </code><code class="sql keyword">name</code><code class="sql plain">,</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql string">'语文'</code> <code class="sql keyword">as</code> <code class="sql plain">subject ,</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql keyword">max</code><code class="sql plain">(</code><code class="sql string">"语文"</code><code class="sql plain">) </code><code class="sql keyword">as</code> <code class="sql plain">score</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql keyword">from</code> <code class="sql plain">student1 </code><code class="sql keyword">group</code> <code class="sql keyword">by</code> <code class="sql keyword">name</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql keyword">union</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql keyword">select</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql spaces"> </code><code class="sql keyword">name</code><code class="sql plain">,</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql spaces"> </code><code class="sql string">'数学'</code> <code class="sql keyword">as</code> <code class="sql plain">subject ,</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql spaces"> </code><code class="sql keyword">max</code><code class="sql plain">(</code><code class="sql string">"数学"</code><code class="sql plain">) </code><code class="sql keyword">as</code> <code class="sql plain">score</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql keyword">from</code> <code class="sql plain">student1 </code><code class="sql keyword">group</code> <code class="sql keyword">by</code> <code class="sql keyword">name</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql keyword">union</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql keyword">select</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql spaces"> </code><code class="sql keyword">name</code><code class="sql plain">,</code>
</div>
<div class="line number15 index14 alt2">
<code class="sql spaces"> </code><code class="sql string">'英语'</code> <code class="sql keyword">as</code> <code class="sql plain">subject ,</code>
</div>
<div class="line number16 index15 alt1">
<code class="sql spaces"> </code><code class="sql keyword">max</code><code class="sql plain">(</code><code class="sql string">"英语"</code><code class="sql plain">) </code><code class="sql keyword">as</code> <code class="sql plain">score</code>
</div>
<div class="line number17 index16 alt2">
<code class="sql keyword">from</code> <code class="sql plain">student1 </code><code class="sql keyword">group</code> <code class="sql keyword">by</code> <code class="sql keyword">name</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
<span><strong>总结</strong></span></p>
<p>
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对的支持。</p>
<p>
原文链接：https://segmentfault.com/a/1190000019220759</p>
頁:
[1]