SQL知识点之列转行Unpivot函数
<p><span><strong>前言</strong></span></p>
<p>
这是总结sql知识点的第二篇文章，一次只总结一个知识点，尽量说明白。上次我们谈到行转列，用的是pivot函数，这次我们来谈谈unpivot函数。（这里是用的数据库是sqlserver，与其他数据库是类似的，大家放心看就好）</p>
<p>
先看一个小问题<img title="SQL知识点之列转行Unpivot函数" alt="SQL知识点之列转行Unpivot函数" id="theimg" src="https://zhuji.jb51.net/uploads/img/202305/5e42d8b042e6ed17d59fa8c0cdb19eb6.jpg" style='font-size: 12px; border-top: 0px; font-family: "microsoft yahei", simsun, arial, sans-serif; border-right: 0px; white-space: normal; word-spacing: 0px; border-bottom: 0px; text-transform: none; font-weight: 400; color: rgb(51,51,51); padding-bottom: 0px; font-style: normal; text-align: left; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; orphans: 2; widows: 2; letter-spacing: normal; padding-right: 0px; background-color: rgb(255,255,255); text-indent: 0px; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial'>customercustomer</p>
<p>
在这张图中，表示的是顾客用不同手机号给phone1、phone2、phone3拨打电话的情况，但是机灵的你，想变个花样来看看，比如下面这样的。</p>
<p>
<img title="SQL知识点之列转行Unpivot函数" alt="SQL知识点之列转行Unpivot函数" id="theimg" src="https://zhuji.jb51.net/uploads/img/202305/58c50b0b240a838e9b2bf2776adc3b5f.jpg" style='font-size: 12px; border-top: 0px; font-family: "microsoft yahei", simsun, arial, sans-serif; border-right: 0px; white-space: normal; word-spacing: 0px; border-bottom: 0px; text-transform: none; font-weight: 400; color: rgb(51,51,51); padding-bottom: 0px; font-style: normal; text-align: left; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; orphans: 2; widows: 2; letter-spacing: normal; padding-right: 0px; background-color: rgb(255,255,255); text-indent: 0px; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial'>unpivotunpivot</p>
<p>
<strong>大家想想看如何实现呢？想下，2分钟后再看哟</strong></p>
<p>
先创建数据</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_757852">
<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>
</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">dbo.customerphones</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">(</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql plain">customerid </code><code class="sql keyword">int</code> <code class="sql keyword">primary</code> <code class="sql keyword">key</code><code class="sql plain">, </code><code class="sql comments">-- fk</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">phone1 </code><code class="sql keyword">varchar</code><code class="sql plain">(32),</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql plain">phone2 </code><code class="sql keyword">varchar</code><code class="sql plain">(32),</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code><code class="sql plain">phone3 </code><code class="sql keyword">varchar</code><code class="sql plain">(32)</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">);</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql keyword">insert</code> <code class="sql plain">dbo.customerphones</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql spaces"> </code><code class="sql plain">(customerid, phone1, phone2, phone3)</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql keyword">values</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql spaces"> </code><code class="sql plain">(1,</code><code class="sql string">'705-491-1111'</code><code class="sql plain">, </code><code class="sql string">'705-491-1110'</code><code class="sql plain">, </code><code class="sql color1">null</code><code class="sql plain">),</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql spaces"> </code><code class="sql plain">(2,</code><code class="sql string">'613-492-2222'</code><code class="sql plain">, </code><code class="sql color1">null</code><code class="sql plain">, </code><code class="sql color1">null</code><code class="sql plain">),</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql spaces"> </code><code class="sql plain">(3,</code><code class="sql string">'416-493-3333'</code><code class="sql plain">, </code><code class="sql string">'416-493-3330'</code><code class="sql plain">, </code><code class="sql string">'416-493-3339'</code><code class="sql plain">);</code>
</div>
<div class="line number14 index13 alt1">
</div>
<div class="line number15 index14 alt2">
<code class="sql spaces"> </code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">dbo.customerphones</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
查看数据如下：</p>
<p>
<img title="SQL知识点之列转行Unpivot函数" alt="SQL知识点之列转行Unpivot函数" id="theimg" src="https://zhuji.jb51.net/uploads/img/202305/1cec94997b20ccba22a515447a6729fc.jpg" style='font-size: 12px; border-top: 0px; font-family: "microsoft yahei", simsun, arial, sans-serif; border-right: 0px; white-space: normal; word-spacing: 0px; border-bottom: 0px; text-transform: none; font-weight: 400; color: rgb(51,51,51); padding-bottom: 0px; font-style: normal; text-align: left; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; orphans: 2; widows: 2; letter-spacing: normal; padding-right: 0px; background-color: rgb(255,255,255); text-indent: 0px; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial'>datadataupivot 实现行转列</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_850100">
<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>
</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 keyword">from</code> <code class="sql plain">dbo.customerphones </code><code class="sql comments">-----数据源</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql spaces"> </code><code class="sql plain">unpivot </code>
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">( </code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">phone </code><code class="sql keyword">for</code> <code class="sql plain">phones </code><code class="sql color1">in</code> <code class="sql plain">(phone1, phone2, phone3)</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql plain">##phone1、phone2、phone3这些列的结果转为行上的结果，成为一列，</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code><code class="sql plain">##并且有了新的列名为phone</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">) up</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
结果如下：</p>
<p>
<img title="SQL知识点之列转行Unpivot函数" alt="SQL知识点之列转行Unpivot函数" id="theimg" src="https://zhuji.jb51.net/uploads/img/202305/aaaca8c107d102c2848e5c7e77776b4d.jpg" style='font-size: 12px; border-top: 0px; font-family: "microsoft yahei", simsun, arial, sans-serif; border-right: 0px; white-space: normal; word-spacing: 0px; border-bottom: 0px; text-transform: none; font-weight: 400; color: rgb(51,51,51); padding-bottom: 0px; font-style: normal; text-align: left; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; orphans: 2; widows: 2; letter-spacing: normal; padding-right: 0px; background-color: rgb(255,255,255); text-indent: 0px; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial'>resultresult</p>
<p>
<strong>参考文献：</strong></p>
<p>
1.use sql server's unpivot operator to help normalize output</p>
<p>
2.sql之行转列pivot用法</p>
<p>
<span><strong>总结</strong></span></p>
<p>
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对的支持。</p>
<p>
原文链接：https://ask.hellobi.com/blog/datafrog/35942</p>
頁:
[1]