sql语句实现行转列的3种方法实例
<p><span><strong>前言</strong></span></p>
<p>
一般在做数据统计的时候会用到行转列，假如要统计学生的成绩，数据库里查询出来的会是这样的，但这并不能达到想要的效果，所以要在查询的时候做一下处理，下面话不多说了，来一起看看详细的介绍。</p>
<p>
<img title="sql语句实现行转列的3种方法实例" alt="sql语句实现行转列的3种方法实例" src="https://zhuji.jb51.net/uploads/img/202305/c9581e9d4a710658ae6b40da0ded976e.jpg"> </p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_430082">
<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>
</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">testtable(</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql spaces"> </code><code class="sql plain"> [</code><code class="sql keyword">int</code><code class="sql plain">] identity(1,1) </code><code class="sql color1">not</code> <code class="sql color1">null</code><code class="sql plain">,</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql plain"> (50) </code><code class="sql color1">null</code><code class="sql plain">,</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain"> (50) </code><code class="sql color1">null</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 keyword">numeric</code><code class="sql plain">](18, 0) </code><code class="sql color1">null</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql plain">) </code><code class="sql keyword">on</code> <code class="sql plain">[</code><code class="sql keyword">primary</code><code class="sql plain">]</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">go</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">testtable (,,) </code>
</div>
<div class="line number9 index8 alt2">
<code class="sql spaces"> </code><code class="sql keyword">select</code> <code class="sql plain">n</code><code class="sql string">'张三'</code><code class="sql plain">,n</code><code class="sql string">'语文'</code><code class="sql plain">,60 </code><code class="sql keyword">union</code> <code class="sql color1">all</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql spaces"> </code><code class="sql keyword">select</code> <code class="sql plain">n</code><code class="sql string">'李四'</code><code class="sql plain">,n</code><code class="sql string">'数学'</code><code class="sql plain">,70 </code><code class="sql keyword">union</code> <code class="sql color1">all</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql spaces"> </code><code class="sql keyword">select</code> <code class="sql plain">n</code><code class="sql string">'王五'</code><code class="sql plain">,n</code><code class="sql string">'英语'</code><code class="sql plain">,80 </code><code class="sql keyword">union</code> <code class="sql color1">all</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql spaces"> </code><code class="sql keyword">select</code> <code class="sql plain">n</code><code class="sql string">'王五'</code><code class="sql plain">,n</code><code class="sql string">'数学'</code><code class="sql plain">,75 </code><code class="sql keyword">union</code> <code class="sql color1">all</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql spaces"> </code><code class="sql keyword">select</code> <code class="sql plain">n</code><code class="sql string">'王五'</code><code class="sql plain">,n</code><code class="sql string">'语文'</code><code class="sql plain">,57 </code><code class="sql keyword">union</code> <code class="sql color1">all</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql spaces"> </code><code class="sql keyword">select</code> <code class="sql plain">n</code><code class="sql string">'李四'</code><code class="sql plain">,n</code><code class="sql string">'语文'</code><code class="sql plain">,80 </code><code class="sql keyword">union</code> <code class="sql color1">all</code>
</div>
<div class="line number15 index14 alt2">
<code class="sql spaces"> </code><code class="sql keyword">select</code> <code class="sql plain">n</code><code class="sql string">'张三'</code><code class="sql plain">,n</code><code class="sql string">'英语'</code><code class="sql plain">,100</code>
</div>
<div class="line number16 index15 alt1">
<code class="sql plain">go</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
这里我用了三种方法来实现行转列第一种：静态行转列</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_178761">
<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>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">username 姓名,</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql color2">sum</code><code class="sql plain">(</code><code class="sql color2">case</code> <code class="sql plain">subject </code><code class="sql keyword">when</code> <code class="sql string">'语文'</code> <code class="sql keyword">then</code> <code class="sql plain">source </code><code class="sql keyword">else</code> <code class="sql plain">0 </code><code class="sql keyword">end</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 plain">subject </code><code class="sql keyword">when</code> <code class="sql string">'数学'</code> <code class="sql keyword">then</code> <code class="sql plain">source </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 color2">sum</code><code class="sql plain">(</code><code class="sql color2">case</code> <code class="sql plain">subject </code><code class="sql keyword">when</code> <code class="sql string">'英语'</code> <code class="sql keyword">then</code> <code class="sql plain">source </code><code class="sql keyword">else</code> <code class="sql plain">0 </code><code class="sql keyword">end</code><code class="sql plain">) 英语 </code><code class="sql keyword">from</code> <code class="sql plain">testtable </code><code class="sql keyword">group</code> <code class="sql keyword">by</code> <code class="sql plain">username</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
用povit行转列</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_624007">
<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>
</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>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql plain">username,subject,source </code><code class="sql keyword">from</code> <code class="sql plain">testtable) testpivot(</code><code class="sql color2">sum</code><code class="sql plain">(source) </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 number3 index2 alt2">
<code class="sql plain">) pvt</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
用存储过程行转列</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_376475">
<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">alter</code> <code class="sql plain">proc pro_test</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">@userimages </code><code class="sql keyword">varchar</code><code class="sql plain">(200),</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">@subject </code><code class="sql keyword">varchar</code><code class="sql plain">(20),</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql plain">@subject1 </code><code class="sql keyword">varchar</code><code class="sql plain">(200),</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">@tablename </code><code class="sql keyword">varchar</code><code class="sql plain">(50)</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql keyword">as</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql spaces"> </code><code class="sql keyword">declare</code> <code class="sql plain">@sql </code><code class="sql keyword">varchar</code><code class="sql plain">(</code><code class="sql keyword">max</code><code class="sql plain">)=</code><code class="sql string">'select * from (select '</code><code class="sql plain">+@userimages+</code><code class="sql string">' from'</code><code class="sql plain">+@tablename+</code><code class="sql string">') tab</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql string">pivot</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql string">(</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql string">sum('</code><code class="sql plain">+@subject+</code><code class="sql string">') for subject('</code><code class="sql plain">+@subject1+</code><code class="sql string">')</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql string">) pvt'</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql keyword">exec</code> <code class="sql plain">(@sql)</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql plain">go</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql keyword">exec</code> <code class="sql plain">pro_test </code><code class="sql string">'username,subject,source'</code><code class="sql plain">,</code>
</div>
<div class="line number15 index14 alt2">
<code class="sql string">'testtable'</code><code class="sql plain">,</code>
</div>
<div class="line number16 index15 alt1">
<code class="sql string">'subject'</code><code class="sql plain">,</code>
</div>
<div class="line number17 index16 alt2">
<code class="sql string">'语文，数学，英语'</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
它们的效果都是这样的</p>
<p>
<img title="sql语句实现行转列的3种方法实例" alt="sql语句实现行转列的3种方法实例" src="https://zhuji.jb51.net/uploads/img/202305/fe16eaf1cf95d13ad3912e7b430dbcda.jpg"></p>
<p>
以上三种方式实现行转列，我们可以根据自己的需求采用不同的方法</p>
<p>
<span><strong>总结</strong></span></p>
<p>
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。</p>
<p>
原文链接：http://www.cnblogs.com/lcq529/p/8400104.html</p>
頁:
[1]