lsm/cnblogs
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
main
cnblogs/dcrenl/SQL Server中行列转换 Pivot UnPivot.html
dcrenl 5e60d01d9f cnblogs.com的html文件
2024-09-24 12:43:01 +08:00

248 lines
94 KiB
HTML
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

<div id="sina_keyword_ad_area2" class="articalContent newfont_family">
<div style="margin-bottom:20px; font-family:Verdana,Arial,Helvetica,sans-serif; line-height:25.2000007629395px; background-color:rgb(255,255,255)">
<p style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:'Times new roman'">PIVOT</span><span style="font-family:宋体">用于将列&#20540;旋转为列名（即行转列），在SQLServer&nbsp;<wbr></span><span style="font-family:'Times new roman'">2000</span><span style="font-family:宋体">可以用聚合函数配合</span><span style="font-family:'Times new roman'">CASE</span><span style="font-family:宋体">语句实现</span></span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:宋体">PIVOT</span><span style="font-family:宋体">的一般语法是：</span><span style="font-family:宋体">PIVOT</span><span style="font-family:宋体">(</span><span style="font-family:宋体">聚合函数(列)FOR
列 in (…) )AS P</span></span></span></p>
<p style="margin:10px auto"><span style="font-family:宋体"><span style="font-size:12px">完整语法：</span></span></p>
<p style="margin:10px auto"><span style="font-size:12px; font-family:'Times new roman'">table_source</span></p>
<p style="margin:10px auto"><span style="font-size:12px; font-family:'Times new roman'">PIVOT(</span></p>
<p style="margin:10px auto; text-indent:21pt"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:宋体">聚合函数（</span><span style="font-family:'Times new roman'">value_column</span><span style="font-family:宋体">）</span></span></span></p>
<p style="margin:10px auto; text-indent:21pt"><span style="font-size:12px; font-family:'Times new roman'">FORpivot_column</span></p>
<p style="margin:10px auto; text-indent:21pt"><span style="font-size:12px; font-family:'Times new roman'">IN()</span></p>
<p style="margin:10px auto"><span style="font-size:12px; font-family:'Times new roman'">)</span></p>
<p style="margin:10px auto">&nbsp;<wbr></p>
<p style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:'Times new roman'">UNPIVOT</span><span style="font-family:宋体">用于将列明转为列&#20540;（即列转行），在</span><span style="font-family:'Times new roman'">SQL Server2000</span><span style="font-family:宋体">可以用</span><span style="font-family:'Times new roman'">UNION</span><span style="font-family:宋体">来实现</span></span></span></p>
<p style="margin:10px auto"><span style="font-family:宋体"><span style="font-size:12px">完整语法：</span></span></p>
<p style="margin:10px auto"><span style="font-size:12px; font-family:'Times new roman'">table_source</span></p>
<p style="margin:10px auto"><span style="font-size:12px; font-family:'Times new roman'">UNPIVOT(</span></p>
<p style="margin:10px auto; text-indent:21pt"><span style="font-size:12px; font-family:'Times new roman'">value_column</span></p>
<p style="margin:10px auto; text-indent:21pt"><span style="font-size:12px; font-family:'Times new roman'">FORpivot_column</span></p>
<p style="margin:10px auto; text-indent:21pt"><span style="font-size:12px; font-family:'Times new roman'">IN()</span></p>
<p style="margin:10px auto"><span style="font-size:12px; font-family:'Times new roman'">)</span></p>
<p style="margin:10px auto">&nbsp;<wbr></p>
<p align="left" style="margin:10px auto"><span style="font-family:宋体"><span style="font-size:12px"><span style="font-size:12px">注意：PIVOT、UNPIVOT是SQL Server2005&nbsp;<wbr></span><span style="font-size:12px">的语法，使用需修改数据库</span><span style="font-size:12px">兼容级别</span></span></span><span style="font-size:9pt; font-family:Tahoma"><br>
&nbsp;<wbr></span><span style="font-family:宋体"><span style="font-size:12px">在数据库属性-&gt;选项-&gt;兼容级别改为&nbsp;<wbr> 90</span></span></p>
<p align="left" style="margin:10px auto">&nbsp;<wbr></p>
<p align="left" style="margin:10px auto"><span style="font-size:12px"><span style="font-family:宋体">典型实例</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-family:宋体"><span style="font-size:12px">一、行转列</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:宋体">1</span><span style="font-family:宋体">、建立表&#26684;</span></span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">if</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:fuchsia">object_id</span><span style="color:gray">(</span><span style="color:red">'tb'</span><span style="color:gray">)</span><span style="color:gray">is</span><span style="color:gray">not</span><span style="color:gray">null</span><span style="color:blue">drop</span><span style="color:blue">table</span>tb</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; font-family:'Courier new'">go</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">create</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">table</span>tb<span style="color:gray">(</span></span><span style="font-size:10pt; font-family:宋体">姓名</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">varchar</span><span style="color:gray">(</span>10<span style="color:gray">),</span></span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">varchar</span><span style="color:gray">(</span>10<span style="color:gray">),</span></span><span style="font-size:10pt; font-family:宋体">分数</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">int</span><span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">insert</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">into</span>tb<span style="color:blue">values</span><span style="color:gray">(</span><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">张三</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">,</span><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">语文</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">,</span>74<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">insert</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">into</span>tb<span style="color:blue">values</span><span style="color:gray">(</span><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">张三</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">,</span><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">数学</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">,</span>83<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">insert</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">into</span>tb<span style="color:blue">values</span><span style="color:gray">(</span><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">张三</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">,</span><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">物理</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">,</span>93<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">insert</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">into</span>tb<span style="color:blue">values</span><span style="color:gray">(</span><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">李四</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">,</span><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">语文</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">,</span>74<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">insert</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">into</span>tb<span style="color:blue">values</span><span style="color:gray">(</span><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">李四</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">,</span><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">数学</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">,</span>84<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">insert</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">into</span>tb<span style="color:blue">values</span><span style="color:gray">(</span><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">李四</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">,</span><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">物理</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">,</span>94<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; font-family:'Courier new'">go</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">select</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">*</span><span style="color:blue">from</span>tb</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; font-family:'Courier new'">go</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">姓名</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">课程</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">分数</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:'Courier new'">---------- ---------------------</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">张三</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">语文</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>74</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">张三</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">数学</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>83</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">张三</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">物理</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>93</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">李四</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">语文</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>74</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">李四</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">数学</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>84</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">李四</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">物理</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>94</span></p>
<p align="left" style="margin:10px auto">&nbsp;<wbr></p>
<p align="left" style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:宋体">2</span><span style="font-family:宋体">、使用SQLServer 2000静态SQL</span></span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:green; font-family:'Courier new'">--c</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">select</span><span style="font-size:10pt; font-family:宋体">姓名</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">,</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; font-family:'Courier new'">&nbsp;<wbr><span style="color:fuchsia">max</span><span style="color:gray">(</span><span style="color:blue">case</span></span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">when</span><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">语文</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">then</span></span><span style="font-size:10pt; font-family:宋体">分数</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">else</span>0<span style="color:blue">end<span style="color:gray">)</span></span></span><span style="font-size:10pt; font-family:宋体">语文</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">,</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; font-family:'Courier new'">&nbsp;<wbr><span style="color:fuchsia">max</span><span style="color:gray">(</span><span style="color:blue">case</span></span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">when</span><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">数学</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">then</span></span><span style="font-size:10pt; font-family:宋体">分数</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">else</span>0<span style="color:blue">end<span style="color:gray">)</span></span></span><span style="font-size:10pt; font-family:宋体">数学</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">,</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; font-family:'Courier new'">&nbsp;<wbr><span style="color:fuchsia">max</span><span style="color:gray">(</span><span style="color:blue">case</span></span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">when</span><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">物理</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">then</span></span><span style="font-size:10pt; font-family:宋体">分数</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">else</span>0<span style="color:blue">end<span style="color:gray">)</span></span></span><span style="font-size:10pt; font-family:宋体">物理</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">from</span><span style="font-size:10pt; font-family:'Courier new'">tb</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">group</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">by</span></span><span style="font-size:10pt; font-family:宋体">姓名</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">姓名</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">语文</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">数学</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">物理</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:'Courier new'">--------------------- ----------- -----------</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">李四</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>74&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>84&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>94</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">张三</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>74&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>83&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>93</span></p>
<p align="left" style="margin:10px auto">&nbsp;<wbr></p>
<p align="left" style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:宋体">3</span><span style="font-family:宋体">、使用SQLServer 2000动态SQL</span></span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:green; font-family:'Courier new'">--SQLSERVER 2000</span><span style="font-size:10pt; color:green; font-family:宋体">动态</span><span style="font-size:10pt; color:green; font-family:'Courier new'">SQL,</span><span style="font-size:10pt; color:green; font-family:宋体">指课程不止语文、数学、物理这三门课程。</span><span style="font-size:10pt; color:green; font-family:'Courier new'">(</span><span style="font-size:10pt; color:green; font-family:宋体">以下同</span><span style="font-size:10pt; color:green; font-family:'Courier new'">)</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:green; font-family:'Courier new'">--</span><span style="font-size:10pt; color:green; font-family:宋体">变量按</span><span style="font-size:10pt; color:green; font-family:'Courier new'">sql</span><span style="font-size:10pt; color:green; font-family:宋体">语言顺序赋&#20540;</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">declare</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:blue">varchar</span><span style="color:gray">(</span>500<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">set</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span><span style="color:red">'select</span></span><span style="font-size:10pt; color:red; font-family:宋体">姓名</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">select</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span>@sql<span style="color:gray">&#43;</span><span style="color:red">',max(case</span></span><span style="font-size:10pt; color:red; font-family:宋体">课程</span><span style="font-size:10pt; color:red; font-family:'Courier new'">when'''</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">&#43;</span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">&#43;</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'''&nbsp;<wbr>then</span><span style="font-size:10pt; color:red; font-family:宋体">分数</span><span style="font-size:10pt; color:red; font-family:'Courier new'">else0
end)['</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">&#43;</span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">&#43;</span><span style="font-size:10pt; color:red; font-family:'Courier new'">']'</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">from</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">(</span><span style="color:blue">select</span><span style="color:blue">distinct</span></span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">from</span>tb<span style="color:gray">)a<span style="color:green">--</span></span></span><span style="font-size:10pt; color:green; font-family:宋体">同</span><span style="font-size:10pt; color:green; font-family:'Courier new'">fromtb
group by</span><span style="font-size:10pt; color:green; font-family:宋体">课程，默认按课程名排序</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">set</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span>@sql<span style="color:gray">&#43;</span><span style="color:red">'from
tb group by</span></span><span style="font-size:10pt; color:red; font-family:宋体">姓名</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">exec</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">(</span>@sql<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto">&nbsp;<wbr></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:green; font-family:'Courier new'">--</span><span style="font-size:10pt; color:green; font-family:宋体">使用</span><span style="font-size:10pt; color:green; font-family:'Courier new'">isnull(),</span><span style="font-size:10pt; color:green; font-family:宋体">变量先确定动态部分</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">declare</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:blue">varchar</span><span style="color:gray">(</span>8000<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">select</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span><span style="color:fuchsia">isnull</span><span style="color:gray">(</span>@sql<span style="color:gray">&#43;</span><span style="color:red">','</span><span style="color:gray">,</span><span style="color:red">''</span><span style="color:gray">)</span><span style="color:gray">&#43;</span><span style="color:red">'max(case</span></span><span style="font-size:10pt; color:red; font-family:宋体">课程</span><span style="font-size:10pt; color:red; font-family:'Courier new'">when'''</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">&#43;</span></span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">&#43;</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:red">'''then</span></span><span style="font-size:10pt; color:red; font-family:宋体">分数</span><span style="font-size:10pt; color:red; font-family:'Courier new'">else0
end) ['</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">&#43;</span></span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">&#43;</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:red">']'</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">from</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">(</span><span style="color:blue">select</span><span style="color:blue">distinct</span></span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">from</span>tb<span style="color:gray">)<span style="color:blue">as</span>a&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">set</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span><span style="color:red">'select</span></span><span style="font-size:10pt; color:red; font-family:宋体">姓名</span><span style="font-size:10pt; color:red; font-family:'Courier new'">,'</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">&#43;</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">&#43;</span><span style="color:red">'from
tb group by</span></span><span style="font-size:10pt; color:red; font-family:宋体">姓名</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">exec</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">(</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">姓名</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">数学</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">物理</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">语文</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:'Courier new'">--------------------- ----------- -----------</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">李四</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>84&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>94&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>74</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">张三</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>83&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>93&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>74</span></p>
<p align="left" style="margin:10px auto">&nbsp;<wbr></p>
<p align="left" style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:宋体">4</span><span style="font-family:宋体">、使用SQLServer 2005静态SQL</span></span></span></p>
<p style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">select</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">*</span><span style="color:blue">from</span>tb&nbsp;<wbr><span style="color:gray">pivot</span><span style="color:gray">(</span><span style="color:fuchsia">max</span><span style="color:gray">(</span></span><span style="font-size:10pt; font-family:宋体">分数</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">)</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">for</span></span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">in</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">(</span></span><span style="font-size:10pt; font-family:宋体">语文</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">,</span><span style="font-size:10pt; font-family:宋体">数学</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">,</span><span style="font-size:10pt; font-family:宋体">物理</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">))</span><span style="font-size:10pt; font-family:'Courier new'">a</span></p>
<p style="margin:10px auto">&nbsp;<wbr></p>
<p style="margin:10px auto"><span style="font-size:10pt; font-family:'Courier new'">5</span><span style="font-size:10pt; font-family:宋体">、</span><span style="font-family:宋体"><span style="font-size:12px">使用SQL Server2005动态SQL</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:green; font-family:'Courier new'">--</span><span style="font-size:10pt; color:green; font-family:宋体">使用</span><span style="font-size:10pt; color:green; font-family:'Courier new'">stuff()</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">declare</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:blue">varchar</span><span style="color:gray">(</span>8000<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">set</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span><span style="color:red">''</span>&nbsp;<wbr>&nbsp;<wbr><span style="color:green">--</span></span><span style="font-size:10pt; color:green; font-family:宋体">初始化变量</span><span style="font-size:10pt; color:green; font-family:'Courier new'">@sql</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">select</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span>@sql<span style="color:gray">&#43;</span><span style="color:red">','</span><span style="color:gray">&#43;</span></span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">from</span>tb<span style="color:blue">groupby</span></span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; color:green; font-family:'Courier new'">--</span><span style="font-size:10pt; color:green; font-family:宋体">变量多&#20540;赋&#20540;</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">set</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span><span style="color:fuchsia">stuff</span><span style="color:gray">(</span>@sql<span style="color:gray">,</span>1<span style="color:gray">,</span>1<span style="color:gray">,</span><span style="color:red">''</span><span style="color:gray">)</span><span style="color:green">--</span></span><span style="font-size:10pt; color:green; font-family:宋体">去掉首个</span><span style="font-size:10pt; color:green; font-family:'Courier new'">','</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">set</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span><span style="color:red">'select* from tb&nbsp;<wbr>pivot
(max(</span></span><span style="font-size:10pt; color:red; font-family:宋体">分数</span><span style="font-size:10pt; color:red; font-family:'Courier new'">)for</span><span style="font-size:10pt; color:red; font-family:宋体">课程</span><span style="font-size:10pt; color:red; font-family:'Courier new'">in('</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">&#43;</span>@sql<span style="color:gray">&#43;</span><span style="color:red">'))a'</span></span></p>
<p style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">exec</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">(</span>@sql<span style="color:gray">)</span></span></p>
<p style="margin:10px auto">&nbsp;<wbr></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:green; font-family:'Courier new'">--</span><span style="font-size:10pt; color:green; font-family:宋体">或使用</span><span style="font-size:10pt; color:green; font-family:'Courier new'">isnull()</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">declare</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:blue">varchar</span><span style="color:gray">(</span>8000<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:green; font-family:'Courier new'">–-</span><span style="font-size:10pt; color:green; font-family:宋体">获得课程集合</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">select</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span><span style="color:fuchsia">isnull</span><span style="color:gray">(</span>@sql<span style="color:gray">&#43;</span><span style="color:red">','</span><span style="color:gray">,</span><span style="color:red">''</span><span style="color:gray">)</span><span style="color:gray">&#43;</span></span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">from</span>tb<span style="color:blue">groupby</span></span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">set</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span><span style="color:red">'select* from tb&nbsp;<wbr>pivot
(max(</span></span><span style="font-size:10pt; color:red; font-family:宋体">分数</span><span style="font-size:10pt; color:red; font-family:'Courier new'">)for</span><span style="font-size:10pt; color:red; font-family:宋体">课程</span><span style="font-size:10pt; color:red; font-family:'Courier new'">in('</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">&#43;</span>@sql<span style="color:gray">&#43;</span><span style="color:red">'))a'</span></span></p>
<p style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">exec</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">(</span>@sql<span style="color:gray">)</span></span></p>
<p style="margin:10px auto">&nbsp;<wbr></p>
<p align="left" style="margin:10px auto"><span style="font-family:宋体"><span style="font-size:12px">二、行转列结果加上总分、平均分</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:宋体">1</span><span style="font-family:宋体">、使用SQLServer 2000静态SQL</span></span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:green; font-family:'Courier new'">--SQLSERVER 2000</span><span style="font-size:10pt; color:green; font-family:宋体">静态</span><span style="font-size:10pt; color:green; font-family:'Courier new'">SQL</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">select</span><span style="font-size:10pt; font-family:宋体">姓名</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">,</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:fuchsia; font-family:'Courier new'">max</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">(</span><span style="font-size:10pt; color:blue; font-family:'Courier new'">case</span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; color:blue; font-family:'Courier new'">when</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">语文</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">then</span></span><span style="font-size:10pt; font-family:宋体">分数</span><span style="font-size:10pt; color:blue; font-family:'Courier new'">else</span><span style="font-size:10pt; font-family:'Courier new'">0<span style="color:blue">end</span><span style="color:gray">)</span></span><span style="font-size:10pt; font-family:宋体">语文</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">,</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:fuchsia; font-family:'Courier new'">max</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">(</span><span style="font-size:10pt; color:blue; font-family:'Courier new'">case</span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; color:blue; font-family:'Courier new'">when</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">数学</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">then</span></span><span style="font-size:10pt; font-family:宋体">分数</span><span style="font-size:10pt; color:blue; font-family:'Courier new'">else</span><span style="font-size:10pt; font-family:'Courier new'">0<span style="color:blue">end</span><span style="color:gray">)</span></span><span style="font-size:10pt; font-family:宋体">数学</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">,</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:fuchsia; font-family:'Courier new'">max</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">(</span><span style="font-size:10pt; color:blue; font-family:'Courier new'">case</span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; color:blue; font-family:'Courier new'">when</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">物理</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">then</span></span><span style="font-size:10pt; font-family:宋体">分数</span><span style="font-size:10pt; color:blue; font-family:'Courier new'">else</span><span style="font-size:10pt; font-family:'Courier new'">0<span style="color:blue">end</span><span style="color:gray">)</span></span><span style="font-size:10pt; font-family:宋体">物理</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">,</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:fuchsia; font-family:'Courier new'">sum</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">(</span><span style="font-size:10pt; font-family:宋体">分数</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">)</span><span style="font-size:10pt; font-family:宋体">总分</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">,</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:fuchsia; font-family:'Courier new'">cast</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">(</span><span style="font-size:10pt; color:fuchsia; font-family:'Courier new'">avg</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">(</span><span style="font-size:10pt; font-family:宋体">分数</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">*</span><span style="font-size:10pt; font-family:'Courier new'">1.0<span style="color:gray">)</span><span style="color:blue">as</span><span style="color:blue">decimal</span><span style="color:gray">(</span>18<span style="color:gray">,</span>2<span style="color:gray">))</span></span><span style="font-size:10pt; font-family:宋体">平均分</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">from</span><span style="font-size:10pt; font-family:'Courier new'">tb</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">group</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">by</span></span><span style="font-size:10pt; font-family:宋体">姓名</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">姓名</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">语文</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">数学</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">物理</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">总分</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">平均分</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:'Courier new'">--------------------- ----------- ----------- -----------</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">李四</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>74&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>84&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>94&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>252&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>84.00</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">张三</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>74&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>83&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>93&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>250&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>83.33</span></p>
<p align="left" style="margin:10px auto">&nbsp;<wbr></p>
<p align="left" style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:宋体">2</span><span style="font-family:宋体">、使用SQLServer 2000动态SQL</span></span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:green; font-family:'Courier new'">--SQLSERVER 2000</span><span style="font-size:10pt; color:green; font-family:宋体">动态</span><span style="font-size:10pt; color:green; font-family:'Courier new'">SQL</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">declare</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:blue">varchar</span><span style="color:gray">(</span>500<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">set</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span><span style="color:red">'select</span></span><span style="font-size:10pt; color:red; font-family:宋体">姓名</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">select</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span>@sql<span style="color:gray">&#43;</span><span style="color:red">',max(case</span></span><span style="font-size:10pt; color:red; font-family:宋体">课程</span><span style="font-size:10pt; color:red; font-family:'Courier new'">when'''</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">&#43;</span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">&#43;</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'''&nbsp;<wbr>then</span><span style="font-size:10pt; color:red; font-family:宋体">分数</span><span style="font-size:10pt; color:red; font-family:'Courier new'">else0
end)['</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">&#43;</span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">&#43;</span><span style="font-size:10pt; color:red; font-family:'Courier new'">']'</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">from</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">(</span><span style="color:blue">select</span><span style="color:blue">distinct</span></span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; color:blue; font-family:'Courier new'">from</span><span style="font-size:10pt; font-family:'Courier new'">tb<span style="color:gray">)</span>a</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">set</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span>@sql<span style="color:gray">&#43;</span><span style="color:red">',sum(</span></span><span style="font-size:10pt; color:red; font-family:宋体">分数</span><span style="font-size:10pt; color:red; font-family:'Courier new'">)</span><span style="font-size:10pt; color:red; font-family:宋体">总分</span><span style="font-size:10pt; color:red; font-family:'Courier new'">,cast(avg(</span><span style="font-size:10pt; color:red; font-family:宋体">分数</span><span style="font-size:10pt; color:red; font-family:'Courier new'">*1.0)as
decimal(18,2))&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:10pt; color:red; font-family:宋体">平均分</span><span style="font-size:10pt; color:red; font-family:'Courier new'">fromtb group by</span><span style="font-size:10pt; color:red; font-family:宋体">姓名</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">exec</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">(</span>@sql<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto">&nbsp;<wbr></p>
<p align="left" style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:宋体">3</span><span style="font-family:宋体">、使用SQLServer 2005静态SQL</span></span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">select</span><span style="font-size:10pt; font-family:'Courier new'">m<span style="color:gray">.*,</span>n<span style="color:gray">.</span></span><span style="font-size:10pt; font-family:宋体">总分</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">,</span><span style="font-size:10pt; font-family:'Courier new'">n<span style="color:gray">.</span></span><span style="font-size:10pt; font-family:宋体">平均分</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">from</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:gray; font-family:'Courier new'">(</span><span style="font-size:10pt; color:blue; font-family:'Courier new'">select</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">*</span><span style="color:blue">from</span>tb&nbsp;<wbr><span style="color:gray">pivot(</span><span style="color:fuchsia">max</span><span style="color:gray">(</span></span><span style="font-size:10pt; font-family:宋体">分数</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">)</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">for</span></span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">in(</span><span style="font-size:10pt; font-family:宋体">语文</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">,</span><span style="font-size:10pt; font-family:宋体">数学</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">,</span><span style="font-size:10pt; font-family:宋体">物理</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">))</span><span style="font-size:10pt; font-family:'Courier new'">a<span style="color:gray">)</span>m<span style="color:gray">,</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:gray; font-family:'Courier new'">(</span><span style="font-size:10pt; color:blue; font-family:'Courier new'">select</span><span style="font-size:10pt; font-family:宋体">姓名</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">,</span><span style="font-size:10pt; color:fuchsia; font-family:'Courier new'">sum</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">(</span><span style="font-size:10pt; font-family:宋体">分数</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">)</span><span style="font-size:10pt; font-family:宋体">总分</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">,</span><span style="font-size:10pt; color:fuchsia; font-family:'Courier new'">cast</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">(</span><span style="font-size:10pt; color:fuchsia; font-family:'Courier new'">avg</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">(</span><span style="font-size:10pt; font-family:宋体">分数</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">*</span><span style="font-size:10pt; font-family:'Courier new'">1.0<span style="color:gray">)</span><span style="color:blue">as</span><span style="color:blue">decimal</span><span style="color:gray">(</span>18<span style="color:gray">,</span>2<span style="color:gray">))</span></span><span style="font-size:10pt; font-family:宋体">平均分</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">from</span><span style="font-size:10pt; font-family:'Courier new'">tb</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">group</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">by</span></span><span style="font-size:10pt; font-family:宋体">姓名</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">)</span><span style="font-size:10pt; font-family:'Courier new'">n</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">where</span><span style="font-size:10pt; font-family:'Courier new'">m<span style="color:gray">.</span></span><span style="font-size:10pt; font-family:宋体">姓名</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">=</span><span style="font-size:10pt; font-family:'Courier new'">n<span style="color:gray">.</span></span><span style="font-size:10pt; font-family:宋体">姓名</span></p>
<p style="margin:10px auto">&nbsp;<wbr></p>
<p style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:宋体">4</span><span style="font-family:宋体">、使用SQLServer 2005动态SQL</span></span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:green; font-family:'Courier new'">--</span><span style="font-size:10pt; color:green; font-family:宋体">使用</span><span style="font-size:10pt; color:green; font-family:'Courier new'">stuff()</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:green; font-family:'Courier new'">--</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">declare</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:blue">varchar</span><span style="color:gray">(</span>8000<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">set</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span><span style="color:red">''</span>&nbsp;<wbr>&nbsp;<wbr><span style="color:green">--</span></span><span style="font-size:10pt; color:green; font-family:宋体">初始化变量</span><span style="font-size:10pt; color:green; font-family:'Courier new'">@sql</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">select</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span>@sql<span style="color:gray">&#43;</span><span style="color:red">','</span><span style="color:gray">&#43;</span></span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">from</span>tb<span style="color:blue">groupby</span></span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; color:green; font-family:'Courier new'">--</span><span style="font-size:10pt; color:green; font-family:宋体">变量多&#20540;赋&#20540;</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:green; font-family:'Courier new'">--</span><span style="font-size:10pt; color:green; font-family:宋体">同</span><span style="font-size:10pt; color:green; font-family:'Courier new'">select@sql
= @sql &#43; ','&#43;</span><span style="font-size:10pt; color:green; font-family:宋体">课程</span><span style="font-size:10pt; color:green; font-family:'Courier new'">from(select distinct</span><span style="font-size:10pt; color:green; font-family:宋体">课程</span><span style="font-size:10pt; color:green; font-family:'Courier new'">fromtb)a</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">set</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span><span style="color:fuchsia">stuff</span><span style="color:gray">(</span>@sql<span style="color:gray">,</span>1<span style="color:gray">,</span>1<span style="color:gray">,</span><span style="color:red">''</span><span style="color:gray">)</span><span style="color:green">--</span></span><span style="font-size:10pt; color:green; font-family:宋体">去掉首个</span><span style="font-size:10pt; color:green; font-family:'Courier new'">','</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">set</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span><span style="color:red">'selectm.* , n.</span></span><span style="font-size:10pt; color:red; font-family:宋体">总分</span><span style="font-size:10pt; color:red; font-family:'Courier new'">,n.</span><span style="font-size:10pt; color:red; font-family:宋体">平均分</span><span style="font-size:10pt; color:red; font-family:'Courier new'">from</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:red; font-family:'Courier new'">(select* from (select * from tb) a pivot (max(</span><span style="font-size:10pt; color:red; font-family:宋体">分数</span><span style="font-size:10pt; color:red; font-family:'Courier new'">)for</span><span style="font-size:10pt; color:red; font-family:宋体">课程</span><span style="font-size:10pt; color:red; font-family:'Courier new'">in('</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">&#43;</span>@sql<span style="color:gray">&#43;</span><span style="color:red">'))
b) m,</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:red; font-family:'Courier new'">(select</span><span style="font-size:10pt; color:red; font-family:宋体">姓名</span><span style="font-size:10pt; color:red; font-family:'Courier new'">,sum(</span><span style="font-size:10pt; color:red; font-family:宋体">分数</span><span style="font-size:10pt; color:red; font-family:'Courier new'">)</span><span style="font-size:10pt; color:red; font-family:宋体">总分</span><span style="font-size:10pt; color:red; font-family:'Courier new'">,cast(avg(</span><span style="font-size:10pt; color:red; font-family:宋体">分数</span><span style="font-size:10pt; color:red; font-family:'Courier new'">*1.0)as
decimal(18,2))</span><span style="font-size:10pt; color:red; font-family:宋体">平均分</span><span style="font-size:10pt; color:red; font-family:'Courier new'">fromtb group by</span><span style="font-size:10pt; color:red; font-family:宋体">姓名</span><span style="font-size:10pt; color:red; font-family:'Courier new'">)n</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:red; font-family:'Courier new'">wherem.</span><span style="font-size:10pt; color:red; font-family:宋体">姓名</span><span style="font-size:10pt; color:red; font-family:'Courier new'">=n.</span><span style="font-size:10pt; color:red; font-family:宋体">姓名</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">exec</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">(</span>@sql<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto">&nbsp;<wbr></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:green; font-family:'Courier new'">--</span><span style="font-size:10pt; color:green; font-family:宋体">或使用</span><span style="font-size:10pt; color:green; font-family:'Courier new'">isnull()</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">declare</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:blue">varchar</span><span style="color:gray">(</span>8000<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">select</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span><span style="color:fuchsia">isnull</span><span style="color:gray">(</span>@sql<span style="color:gray">&#43;</span><span style="color:red">','</span><span style="color:gray">,</span><span style="color:red">''</span><span style="color:gray">)</span><span style="color:gray">&#43;</span></span><span style="font-size:10pt; font-family:宋体">课程</span><span style="font-size:10pt; color:blue; font-family:'Courier new'">from</span><span style="font-size:10pt; font-family:'Courier new'">tb<span style="color:blue">group</span><span style="color:blue">by</span></span><span style="font-size:10pt; font-family:宋体">课程</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">set</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span><span style="color:red">'selectm.* , n.</span></span><span style="font-size:10pt; color:red; font-family:宋体">总分</span><span style="font-size:10pt; color:red; font-family:'Courier new'">,n.</span><span style="font-size:10pt; color:red; font-family:宋体">平均分</span><span style="font-size:10pt; color:red; font-family:'Courier new'">from</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:red; font-family:'Courier new'">(select* from (select * from tb) a pivot (max(</span><span style="font-size:10pt; color:red; font-family:宋体">分数</span><span style="font-size:10pt; color:red; font-family:'Courier new'">)for</span><span style="font-size:10pt; color:red; font-family:宋体">课程</span><span style="font-size:10pt; color:red; font-family:'Courier new'">in('</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">&#43;</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; font-family:'Courier new'">&nbsp;<wbr>@sql<span style="color:gray">&#43;</span><span style="color:red">'))b) m ,</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:red; font-family:'Courier new'">(select</span><span style="font-size:10pt; color:red; font-family:宋体">姓名</span><span style="font-size:10pt; color:red; font-family:'Courier new'">,sum(</span><span style="font-size:10pt; color:red; font-family:宋体">分数</span><span style="font-size:10pt; color:red; font-family:'Courier new'">)</span><span style="font-size:10pt; color:red; font-family:宋体">总分</span><span style="font-size:10pt; color:red; font-family:'Courier new'">,cast(avg(</span><span style="font-size:10pt; color:red; font-family:宋体">分数</span><span style="font-size:10pt; color:red; font-family:'Courier new'">*1.0)as
decimal(18,2))</span><span style="font-size:10pt; color:red; font-family:宋体">平均分</span><span style="font-size:10pt; color:red; font-family:'Courier new'">fromtb group by</span><span style="font-size:10pt; color:red; font-family:宋体">姓名</span><span style="font-size:10pt; color:red; font-family:'Courier new'">)n</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:red; font-family:'Courier new'">wherem.</span><span style="font-size:10pt; color:red; font-family:宋体">姓名</span><span style="font-size:10pt; color:red; font-family:'Courier new'">=n.</span><span style="font-size:10pt; color:red; font-family:宋体">姓名</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span></p>
<p style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">exec</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">(</span>@sql<span style="color:gray">)</span></span></p>
<p style="margin:10px auto">&nbsp;<wbr></p>
<p style="margin:10px auto"><span style="font-size:12px"><span style="font-family:宋体">二、列转行</span></span></p>
<p style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:'Courier new'">1</span><span style="font-family:宋体">、建立表&#26684;</span></span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">if</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:fuchsia">object_id</span><span style="color:gray">(</span><span style="color:red">'tb'</span><span style="color:gray">)</span><span style="color:gray">is</span><span style="color:gray">not</span><span style="color:gray">null</span><span style="color:blue">drop</span><span style="color:blue">table</span>tb</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; font-family:'Courier new'">go</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">create</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">table</span>tb<span style="color:gray">(</span></span><span style="font-size:10pt; font-family:宋体">姓名</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">varchar</span><span style="color:gray">(</span>10<span style="color:gray">),</span></span><span style="font-size:10pt; font-family:宋体">语文</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">int</span><span style="color:gray">,</span></span><span style="font-size:10pt; font-family:宋体">数学</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">int</span><span style="color:gray">,</span></span><span style="font-size:10pt; font-family:宋体">物理</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">int</span><span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">insert</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">into</span>tb<span style="color:blue">values</span><span style="color:gray">(</span><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">张三</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">,</span><span style="font-size:10pt; font-family:'Courier new'">74<span style="color:gray">,</span>83<span style="color:gray">,</span>93<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">insert</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">into</span>tb<span style="color:blue">values</span><span style="color:gray">(</span><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">李四</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">,</span><span style="font-size:10pt; font-family:'Courier new'">74<span style="color:gray">,</span>84<span style="color:gray">,</span>94<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; font-family:'Courier new'">go</span></p>
<p style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">select</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">*</span><span style="color:blue">from</span>tb</span></p>
<p style="margin:10px auto"><span style="font-family:'Courier new'"><span style="font-size:12px">go</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">姓名</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">语文</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">数学</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">物理</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:'Courier new'">--------------------- ----------- -----------</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">张三</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>74&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>83&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>93</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">李四</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>74&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>84&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>94</span></p>
<p style="margin:10px auto">&nbsp;<wbr></p>
<p align="left" style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:宋体">2</span><span style="font-family:宋体">、使用SQLServer 2000静态SQL</span></span></span></p>
<p style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="color:teal"><span style="font-family:'Times new roman'">--SQLSERVER 2000</span></span><span style="color:teal; font-family:宋体">静态</span><span style="color:teal"><span style="font-family:'Times new roman'">SQL</span></span><span style="color:teal; font-family:宋体">。</span></span></span></p>
<p style="margin:10px auto"><span style="font-size:12px"><span style="font-family:'Times new roman'"><span style="font-size:12px"><span style="color:blue">select</span><span style="color:gray">*<span style="color:blue">from</span></span></span></span></span></p>
<p style="margin:10px auto"><span style="font-size:12px; font-family:'Times new roman'">(</span></p>
<p style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:'Times new roman'">&nbsp;<wbr><span style="color:blue">select</span></span><span style="font-family:宋体">姓名</span><span style="font-family:'Times new roman'">,</span><span style="font-family:宋体">课程</span><span style="font-family:'Times new roman'"><span style="color:gray">=</span><span style="color:red">'</span></span><span style="color:red; font-family:宋体">语文</span><span style="font-family:'Times new roman'"><span style="color:red">'</span>,</span><span style="font-family:宋体">分数</span><span style="font-family:'Times new roman'"><span style="color:gray">=</span></span><span style="font-family:宋体">语文</span><span style="font-family:'Times new roman'"><span style="color:blue">from</span>tb</span></span></span></p>
<p style="margin:10px auto"><span style="font-size:12px"><span style="font-family:'Times new roman'"><span style="font-size:12px">&nbsp;<wbr><span style="color:blue">union<span style="color:gray">all</span></span></span></span></span></p>
<p style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:'Times new roman'">&nbsp;<wbr><span style="color:blue">select</span></span><span style="font-family:宋体">姓名</span><span style="font-family:'Times new roman'">,</span><span style="font-family:宋体">课程</span><span style="font-family:'Times new roman'"><span style="color:gray">=</span><span style="color:red">'</span></span><span style="color:red; font-family:宋体">数学</span><span style="font-family:'Times new roman'"><span style="color:red">'</span>,</span><span style="font-family:宋体">分数</span><span style="font-family:'Times new roman'"><span style="color:gray">=</span></span><span style="font-family:宋体">数学</span><span style="font-family:'Times new roman'"><span style="color:blue">from</span>tb</span></span></span></p>
<p style="margin:10px auto"><span style="font-size:12px"><span style="font-family:'Times new roman'"><span style="font-size:12px">&nbsp;<wbr><span style="color:blue">union<span style="color:gray">all</span></span></span></span></span></p>
<p style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:'Times new roman'">&nbsp;<wbr><span style="color:blue">select</span></span><span style="font-family:宋体">姓名</span><span style="font-family:'Times new roman'">,</span><span style="font-family:宋体">课程</span><span style="font-family:'Times new roman'"><span style="color:gray">=</span><span style="color:red">'</span></span><span style="color:red; font-family:宋体">物理</span><span style="font-family:'Times new roman'"><span style="color:red">'</span>,</span><span style="font-family:宋体">分数</span><span style="font-family:'Times new roman'"><span style="color:gray">=</span></span><span style="font-family:宋体">物理</span><span style="font-family:'Times new roman'"><span style="color:blue">from</span>tb</span></span></span></p>
<p style="margin:10px auto"><span style="font-size:12px; font-family:'Times new roman'">) t</span></p>
<p style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:'Times new roman'"><span style="color:blue">order</span><span style="color:blue">by</span></span><span style="font-family:宋体">姓名</span><span style="font-family:'Times new roman'">,<span style="color:fuchsia">case</span></span><span style="font-family:宋体">课程</span><span style="font-family:'Times new roman'"><span style="color:blue">when</span><span style="color:red">'</span></span><span style="color:red; font-family:宋体">语文</span><span style="font-family:'Times new roman'"><span style="color:red">'</span><span style="color:blue">then<strong><span style="color:maroon">1</span></strong>when<span style="color:red">'</span></span></span><span style="color:red; font-family:宋体">数学</span><span style="font-family:'Times new roman'"><span style="color:red">'</span><span style="color:blue">then<strong><span style="color:maroon">2</span></strong>when<span style="color:red">'</span></span></span><span style="color:red; font-family:宋体">物理</span><span style="font-family:'Times new roman'"><span style="color:red">'</span><span style="color:blue">then<strong><span style="color:maroon">3</span></strong>end</span></span></span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">姓名</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">课程</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">分数</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:'Courier new'">---------- ---------------</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">李四</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">语文</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>74</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">李四</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">数学</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>84</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">李四</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">物理</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>94</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">张三</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">语文</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>74</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">张三</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">数学</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>83</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:9pt; font-family:宋体">张三</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr>&nbsp;<wbr></span><span style="font-size:9pt; font-family:宋体">物理</span><span style="font-size:9pt; font-family:'Courier new'">&nbsp;<wbr>93</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:8pt; font-family:'Courier new'">&nbsp;<wbr>&nbsp;<wbr></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:宋体">2</span><span style="font-family:宋体">、使用SQLServer 2000动态SQL</span></span></span></p>
<p style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="color:teal"><span style="font-family:'Times new roman'">--SQLSERVER 2000</span></span><span style="color:teal; font-family:宋体">动态</span><span style="color:teal"><span style="font-family:'Times new roman'">SQL</span></span><span style="color:teal; font-family:宋体">。</span></span></span></p>
<p style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="color:teal"><span style="font-family:'Times new roman'">--</span></span><span style="color:teal; font-family:宋体">调用系统表动态生态。</span></span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">declare</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:blue">varchar</span><span style="color:gray">(</span>8000<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">select</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span><span style="color:fuchsia">isnull</span><span style="color:gray">(</span>@sql<span style="color:gray">&#43;</span><span style="color:red">'union
all '</span><span style="color:gray">,</span><span style="color:red">''</span><span style="color:gray">)</span><span style="color:gray">&#43;</span><span style="color:red">'select</span></span><span style="font-size:10pt; color:red; font-family:宋体">姓名</span><span style="font-size:10pt; color:red; font-family:'Courier new'">,[</span><span style="font-size:10pt; color:red; font-family:宋体">课程</span><span style="font-size:10pt; color:red; font-family:'Courier new'">]='</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:gray; font-family:'Courier new'">&#43;</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:fuchsia">quotename</span><span style="color:gray">(</span><span style="color:blue">Name</span><span style="color:gray">,</span><span style="color:red">''''</span><span style="color:gray">)</span><span style="color:gray">&#43;</span><span style="color:red">',
[</span></span><span style="font-size:10pt; color:red; font-family:宋体">分数</span><span style="font-size:10pt; color:red; font-family:'Courier new'">]= '</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">&#43;</span><span style="color:fuchsia">quotename</span><span style="color:gray">(</span><span style="color:blue">Name</span><span style="color:gray">)</span><span style="color:gray">&#43;</span><span style="color:red">'
fromtb'</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">from</span><span style="font-size:10pt; font-family:'Courier new'">syscolumns</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">where</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">Name</span><span style="color:gray">!=</span><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">姓名</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:gray">and</span>ID<span style="color:gray">=</span><span style="color:fuchsia">object_id</span><span style="color:gray">(</span><span style="color:red">'tb'</span><span style="color:gray">)</span><span style="color:green">--</span></span><span style="font-size:10pt; color:green; font-family:宋体">表名</span><span style="font-size:10pt; color:green; font-family:'Courier new'">tb</span><span style="font-size:10pt; color:green; font-family:宋体">，不包含列名为姓名的其他列</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">order</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">by</span>colid</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">exec</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">(</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">&#43;</span><span style="color:red">'order
by</span></span><span style="font-size:10pt; color:red; font-family:宋体">姓名</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">)</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; font-family:'Courier new'">go</span></p>
<p align="left" style="margin:10px auto">&nbsp;<wbr></p>
<p align="left" style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:宋体">3</span><span style="font-family:宋体">、使用SQLServer 2005静态SQL</span></span></span></p>
<p style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="color:teal"><span style="font-family:'Times new roman'">--SQLSERVER 2005</span></span><span style="color:teal; font-family:宋体">动态</span><span style="color:teal"><span style="font-family:'Times new roman'">SQL</span></span></span></span></p>
<p style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:'Times new roman'"><span style="color:blue">select</span></span><span style="font-family:宋体">姓名</span><span style="font-family:'Times new roman'">,</span><span style="font-family:宋体">课程</span><span style="font-family:'Times new roman'">,</span><span style="font-family:宋体">分数</span><span style="font-family:'Times new roman'"><span style="color:blue">from</span>tb
unpivot (</span><span style="font-family:宋体">分数</span><span style="font-family:'Times new roman'"><span style="color:blue">for</span></span><span style="font-family:宋体">课程</span><span style="font-family:'Times new roman'"><span style="color:gray">in</span>(<span style="color:red">[</span></span><span style="color:red; font-family:宋体">语文</span><span style="font-family:'Times new roman'"><span style="color:red">]</span>,<span style="color:red">[</span></span><span style="color:red; font-family:宋体">数学</span><span style="font-family:'Times new roman'"><span style="color:red">]</span>,<span style="color:red">[</span></span><span style="color:red; font-family:宋体">物理</span><span style="font-family:'Times new roman'"><span style="color:red">]</span>))
t</span></span></span></p>
<p align="left" style="margin:10px auto">&nbsp;<wbr></p>
<p style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="font-family:宋体">4</span><span style="font-family:宋体">、使用SQLServer 2005动态SQL</span></span></span></p>
<p style="margin:10px auto"><span style="font-size:12px"><span style="font-size:12px"><span style="color:teal"><span style="font-family:'Times new roman'">--SQLSERVER 2005</span></span><span style="color:teal; font-family:宋体">动态</span><span style="color:teal"><span style="font-family:'Times new roman'">SQL</span></span></span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">declare</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:blue">nvarchar</span><span style="color:gray">(</span>4000<span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">select</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span><span style="color:fuchsia">isnull</span><span style="color:gray">(</span>@sql<span style="color:gray">&#43;</span><span style="color:red">','</span><span style="color:gray">,</span><span style="color:red">''</span><span style="color:gray">)&#43;</span><span style="color:fuchsia">quotename</span><span style="color:gray">(</span><span style="color:blue">Name</span><span style="color:gray">)</span></span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">from</span><span style="font-size:10pt; font-family:'Courier new'">syscolumns</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">where</span><span style="font-size:10pt; font-family:'Courier new'">ID<span style="color:gray">=</span><span style="color:fuchsia">object_id</span><span style="color:gray">(</span><span style="color:red">'tb'</span><span style="color:gray">)</span><span style="color:gray">and</span><span style="color:blue">Name</span><span style="color:gray">not</span><span style="color:gray">in(</span><span style="color:red">'</span></span><span style="font-size:10pt; color:red; font-family:宋体">姓名</span><span style="font-size:10pt; color:red; font-family:'Courier new'">'</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">)</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">order</span><span style="font-size:10pt; font-family:'Courier new'"><span style="color:blue">by</span>Colid</span></p>
<p align="left" style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">set</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">=</span><span style="color:red">'select</span></span><span style="font-size:10pt; color:red; font-family:宋体">姓名</span><span style="font-size:10pt; color:red; font-family:'Courier new'">,[</span><span style="font-size:10pt; color:red; font-family:宋体">课程</span><span style="font-size:10pt; color:red; font-family:'Courier new'">],[</span><span style="font-size:10pt; color:red; font-family:宋体">分数</span><span style="font-size:10pt; color:red; font-family:'Courier new'">]from
tb unpivot ([</span><span style="font-size:10pt; color:red; font-family:宋体">分数</span><span style="font-size:10pt; color:red; font-family:'Courier new'">]for [</span><span style="font-size:10pt; color:red; font-family:宋体">课程</span><span style="font-size:10pt; color:red; font-family:'Courier new'">]in('</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">&#43;</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">&#43;</span><span style="color:red">'))b'</span></span></p>
<p style="margin:10px auto"><span style="font-size:10pt; color:blue; font-family:'Courier new'">exec</span><span style="font-size:10pt; color:gray; font-family:'Courier new'">(</span><span style="font-size:10pt; font-family:'Courier new'">@sql<span style="color:gray">)</span></span></p>
<br>
</div>
<div style="font-family:Verdana,Arial,Helvetica,sans-serif; line-height:25.2000007629395px; background-color:rgb(255,255,255)">
该文章经过本人整理所得,欢迎转载,转载时请加上本文地址;本文基于<a target="_blank" title="Creative Commons Attribution 2.5 China Mainland License" href="http://creativecommons.org/licenses/by/2.5/cn/" target="_blank"><span style="color:#6699CC">署名2.5 中国大陆</span></a>许可协议发布，请勿演绎或用于商业目的，但是必须保留本文的署名<a target="_blank" href="http://www.cnblogs.com/zhangzt/"><span style="color:#6699CC">张志涛</span></a></div>
</div>
Reference in New Issue View Git Blame Copy Permalink