Mikel
[转载]重温SQL——行转列，列转行 – 潇湘隐者 – 博客园.
行转列，列转行是我们在开发过程中经常碰到的问题。行转列一般通过CASE WHEN 语句来实现，也可以通过 SQL SERVER 2005 新增的运算符PIVOT来实现。 用传统的方法，比较好理解。层次清晰，而且比较习惯。 但是PIVOT 、UNPIVOT提供的语法比一系列复杂的 SELECT…CASE 语句中所指定的语法更简单、更具可读性。下面我们通过几个简单的例子来介绍一下列转行、行转列问题。
我们首先先通过一个老生常谈的例子，学生成绩表(下面简化了些)来形象了解下行转列
<span class="pln">CREATE TABLE </span><span class="pun">[</span><span class="typ">StudentScores</span><span class="pun">]</span><span class="pln"></span><span class="pun">(</span><span class="pln"> </span><span class="pun">[</span><span class="typ">UserName</span><span class="pun">]</span><span class="pln"> NVARCHAR</span><span class="pun">(</span><span class="lit">20</span><span class="pun">),</span><span class="pln"> </span><span class="pun">--学生姓名</span><span class="pln"> </span><span class="pun">[</span><span class="typ">Subject</span><span class="pun">]</span><span class="pln"> NVARCHAR</span><span class="pun">(</span><span class="lit">30</span><span class="pun">),</span><span class="pln"> </span><span class="pun">--科目</span><span class="pln"> </span><span class="pun">[</span><span class="typ">Score</span><span class="pun">]</span><span class="pln"> FLOAT</span><span class="pun">,</span><span class="pln"> </span><span class="pun">--成绩</span><span class="pln"></span><span class="pun">)</span><span class="pln">INSERT INTO </span><span class="pun">[</span><span class="typ">StudentScores</span><span class="pun">]</span><span class="pln"> SELECT </span><span class="str">'Nick'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'语文'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">80</span><span class="pln">INSERT INTO </span><span class="pun">[</span><span class="typ">StudentScores</span><span class="pun">]</span><span class="pln"> SELECT </span><span class="str">'Nick'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'数学'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">90</span><span class="pln">INSERT INTO </span><span class="pun">[</span><span class="typ">StudentScores</span><span class="pun">]</span><span class="pln"> SELECT </span><span class="str">'Nick'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'英语'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">70</span><span class="pln">INSERT INTO </span><span class="pun">[</span><span class="typ">StudentScores</span><span class="pun">]</span><span class="pln"> SELECT </span><span class="str">'Nick'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'生物'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">85</span><span class="pln">INSERT INTO </span><span class="pun">[</span><span class="typ">StudentScores</span><span class="pun">]</span><span class="pln"> SELECT </span><span class="str">'Kent'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'语文'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">80</span><span class="pln">INSERT INTO </span><span class="pun">[</span><span class="typ">StudentScores</span><span class="pun">]</span><span class="pln"> SELECT </span><span class="str">'Kent'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'数学'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">90</span><span class="pln">INSERT INTO </span><span class="pun">[</span><span class="typ">StudentScores</span><span class="pun">]</span><span class="pln"> SELECT </span><span class="str">'Kent'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'英语'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">70</span><span class="pln">INSERT INTO </span><span class="pun">[</span><span class="typ">StudentScores</span><span class="pun">]</span><span class="pln"> SELECT </span><span class="str">'Kent'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'生物'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">85</span> |
如果我想知道每位学生的每科成绩，而且每个学生的全部成绩排成一行，这样方便我查看、统计，导出数据
<span class="pln">SELECT </span><span class="typ">UserName</span><span class="pun">,</span><span class="pln"> MAX</span><span class="pun">(</span><span class="pln">CASE </span><span class="typ">Subject</span><span class="pln"> WHEN </span><span class="str">'语文'</span><span class="pln"> THEN </span><span class="typ">Score</span><span class="pln"> ELSE </span><span class="lit">0</span><span class="pln"> </span><span class="kwd">END</span><span class="pun">)</span><span class="pln"> AS </span><span class="str">'语文'</span><span class="pun">,</span><span class="pln"> MAX</span><span class="pun">(</span><span class="pln">CASE </span><span class="typ">Subject</span><span class="pln"> WHEN </span><span class="str">'数学'</span><span class="pln"> THEN </span><span class="typ">Score</span><span class="pln"> ELSE </span><span class="lit">0</span><span class="pln"> </span><span class="kwd">END</span><span class="pun">)</span><span class="pln"> AS </span><span class="str">'数学'</span><span class="pun">,</span><span class="pln"> MAX</span><span class="pun">(</span><span class="pln">CASE </span><span class="typ">Subject</span><span class="pln"> WHEN </span><span class="str">'英语'</span><span class="pln"> THEN </span><span class="typ">Score</span><span class="pln"> ELSE </span><span class="lit">0</span><span class="pln"> </span><span class="kwd">END</span><span class="pun">)</span><span class="pln"> AS </span><span class="str">'英语'</span><span class="pun">,</span><span class="pln"> MAX</span><span class="pun">(</span><span class="pln">CASE </span><span class="typ">Subject</span><span class="pln"> WHEN </span><span class="str">'生物'</span><span class="pln"> THEN </span><span class="typ">Score</span><span class="pln"> ELSE </span><span class="lit">0</span><span class="pln"> </span><span class="kwd">END</span><span class="pun">)</span><span class="pln"> AS </span><span class="str">'生物'</span><span class="pln">FROM dbo</span><span class="pun">.[</span><span class="typ">StudentScores</span><span class="pun">]</span><span class="pln">GROUP BY </span><span class="typ">UserName</span> |
查询结果如图所示，这样我们就能很清楚的了解每位学生所有的成绩了
接下来我们来看看第二个小列子。有一个游戏玩家充值表（仅仅为了说明，举的一个小例子）
<span class="pln">CREATE TABLE </span><span class="pun">[</span><span class="typ">Inpours</span><span class="pun">]</span><span class="pln"></span><span class="pun">(</span><span class="pln"> </span><span class="pun">[</span><span class="pln">ID</span><span class="pun">]</span><span class="pln"> INT IDENTITY</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="lit">1</span><span class="pun">),</span><span class="pln"> </span><span class="pun">[</span><span class="typ">UserName</span><span class="pun">]</span><span class="pln"> NVARCHAR</span><span class="pun">(</span><span class="lit">20</span><span class="pun">),</span><span class="pln"> </span><span class="pun">--游戏玩家</span><span class="pln"> </span><span class="pun">[</span><span class="typ">CreateTime</span><span class="pun">]</span><span class="pln"> DATETIME</span><span class="pun">,</span><span class="pln"> </span><span class="pun">--充值时间</span><span class="pln"> </span><span class="pun">[</span><span class="typ">PayType</span><span class="pun">]</span><span class="pln"> NVARCHAR</span><span class="pun">(</span><span class="lit">20</span><span class="pun">),</span><span class="pln"> </span><span class="pun">--充值类型</span><span class="pln"> </span><span class="pun">[</span><span class="typ">Money</span><span class="pun">]</span><span class="pln"> DECIMAL</span><span class="pun">,</span><span class="pln"> </span><span class="pun">--充值金额</span><span class="pln"> </span><span class="pun">[</span><span class="typ">IsSuccess</span><span class="pun">]</span><span class="pln"> BIT</span><span class="pun">,</span><span class="pln"> </span><span class="pun">--是否成功</span><span class="pln"> </span><span class="lit">1</span><span class="pun">表示成功，</span><span class="pln"> </span><span class="lit">0</span><span class="pun">表示失败</span><span class="pln"> CONSTRAINT </span><span class="pun">[</span><span class="pln">PK_Inpours_ID</span><span class="pun">]</span><span class="pln"> PRIMARY KEY</span><span class="pun">(</span><span class="pln">ID</span><span class="pun">)</span><span class="pln"></span><span class="pun">)</span><span class="pln">INSERT INTO </span><span class="typ">Inpours</span><span class="pln"> SELECT </span><span class="str">'张三'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2010-05-01'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'支付宝'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">50</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln">INSERT INTO </span><span class="typ">Inpours</span><span class="pln"> SELECT </span><span class="str">'张三'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2010-06-14'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'支付宝'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">50</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln">INSERT INTO </span><span class="typ">Inpours</span><span class="pln"> SELECT </span><span class="str">'张三'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2010-06-14'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'手机短信'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">100</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln">INSERT INTO </span><span class="typ">Inpours</span><span class="pln"> SELECT </span><span class="str">'李四'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2010-06-14'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'手机短信'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">100</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln">INSERT INTO </span><span class="typ">Inpours</span><span class="pln"> SELECT </span><span class="str">'李四'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2010-07-14'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'支付宝'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">100</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln">INSERT INTO </span><span class="typ">Inpours</span><span class="pln"> SELECT </span><span class="str">'王五'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2010-07-14'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'工商银行卡'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">100</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln">INSERT INTO </span><span class="typ">Inpours</span><span class="pln"> SELECT </span><span class="str">'赵六'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2010-07-14'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'建设银行卡'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">100</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span> |
下面来了一个统计数据的需求，要求按日期、支付方式来统计充值金额信息。这也是一个典型的行转列的例子。
我们可以通过下面的脚本来达到目的:
<span class="pln">SELECT CONVERT</span><span class="pun">(</span><span class="pln">VARCHAR</span><span class="pun">(</span><span class="lit">10</span><span class="pun">),</span><span class="pln"> </span><span class="typ">CreateTime</span><span class="pun">,</span><span class="pln"> </span><span class="lit">120</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">CreateTime</span><span class="pun">,</span><span class="pln"> CASE </span><span class="typ">PayType</span><span class="pln"> WHEN </span><span class="str">'支付宝'</span><span class="pln"> THEN SUM</span><span class="pun">(</span><span class="typ">Money</span><span class="pun">)</span><span class="pln"> ELSE </span><span class="lit">0</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> AS </span><span class="str">'支付宝'</span><span class="pun">,</span><span class="pln"> CASE </span><span class="typ">PayType</span><span class="pln"> WHEN </span><span class="str">'手机短信'</span><span class="pln"> THEN SUM</span><span class="pun">(</span><span class="typ">Money</span><span class="pun">)</span><span class="pln"> ELSE </span><span class="lit">0</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> AS </span><span class="str">'手机短信'</span><span class="pun">,</span><span class="pln"> CASE </span><span class="typ">PayType</span><span class="pln"> WHEN </span><span class="str">'工商银行卡'</span><span class="pln"> THEN SUM</span><span class="pun">(</span><span class="typ">Money</span><span class="pun">)</span><span class="pln"> ELSE </span><span class="lit">0</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> AS </span><span class="str">'工商银行卡'</span><span class="pun">,</span><span class="pln"> CASE </span><span class="typ">PayType</span><span class="pln"> WHEN </span><span class="str">'建设银行卡'</span><span class="pln"> THEN SUM</span><span class="pun">(</span><span class="typ">Money</span><span class="pun">)</span><span class="pln"> ELSE </span><span class="lit">0</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> AS </span><span class="str">'建设银行卡'</span><span class="pln">FROM </span><span class="typ">Inpours</span><span class="pln">GROUP BY </span><span class="typ">CreateTime</span><span class="pun">,</span><span class="pln"> </span><span class="typ">PayType</span> |
如图所示，我们这样只是得到了这样的输出结果，还需进一步处理，才能得到想要的结果
<span class="pln">SELECT </span><span class="typ">CreateTime</span><span class="pun">,</span><span class="pln"> ISNULL</span><span class="pun">(</span><span class="pln">SUM</span><span class="pun">([支付宝]),</span><span class="pln"> </span><span class="lit">0</span><span class="pun">)</span><span class="pln"> AS </span><span class="pun">[支付宝],</span><span class="pln"> ISNULL</span><span class="pun">(</span><span class="pln">SUM</span><span class="pun">([手机短信]),</span><span class="pln"> </span><span class="lit">0</span><span class="pun">)</span><span class="pln"> AS </span><span class="pun">[手机短信],</span><span class="pln"> ISNULL</span><span class="pun">(</span><span class="pln">SUM</span><span class="pun">([工商银行卡]),</span><span class="pln"> </span><span class="lit">0</span><span class="pun">)</span><span class="pln"> AS </span><span class="pun">[工商银行卡],</span><span class="pln"> ISNULL</span><span class="pun">(</span><span class="pln">SUM</span><span class="pun">([建设银行卡]),</span><span class="pln"> </span><span class="lit">0</span><span class="pun">)</span><span class="pln"> AS </span><span class="pun">[建设银行卡]</span><span class="pln">FROM</span><span class="pun">(</span><span class="pln"> SELECT CONVERT</span><span class="pun">(</span><span class="pln">VARCHAR</span><span class="pun">(</span><span class="lit">10</span><span class="pun">),</span><span class="pln"> </span><span class="typ">CreateTime</span><span class="pun">,</span><span class="pln"> </span><span class="lit">120</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">CreateTime</span><span class="pun">,</span><span class="pln"> CASE </span><span class="typ">PayType</span><span class="pln"> WHEN </span><span class="str">'支付宝'</span><span class="pln"> THEN SUM</span><span class="pun">(</span><span class="typ">Money</span><span class="pun">)</span><span class="pln"> ELSE </span><span class="lit">0</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> AS </span><span class="str">'支付宝'</span><span class="pun">,</span><span class="pln"> CASE </span><span class="typ">PayType</span><span class="pln"> WHEN </span><span class="str">'手机短信'</span><span class="pln"> THEN SUM</span><span class="pun">(</span><span class="typ">Money</span><span class="pun">)</span><span class="pln"> ELSE </span><span class="lit">0</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> AS </span><span class="str">'手机短信'</span><span class="pun">,</span><span class="pln"> CASE </span><span class="typ">PayType</span><span class="pln"> WHEN </span><span class="str">'工商银行卡'</span><span class="pln"> THEN SUM</span><span class="pun">(</span><span class="typ">Money</span><span class="pun">)</span><span class="pln"> ELSE </span><span class="lit">0</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> AS </span><span class="str">'工商银行卡'</span><span class="pun">,</span><span class="pln"> CASE </span><span class="typ">PayType</span><span class="pln"> WHEN </span><span class="str">'建设银行卡'</span><span class="pln"> THEN SUM</span><span class="pun">(</span><span class="typ">Money</span><span class="pun">)</span><span class="pln"> ELSE </span><span class="lit">0</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> AS </span><span class="str">'建设银行卡'</span><span class="pln"> FROM </span><span class="typ">Inpours</span><span class="pln"> GROUP BY </span><span class="typ">CreateTime</span><span class="pun">,</span><span class="pln"> </span><span class="typ">PayType</span><span class="pln"></span><span class="pun">)</span><span class="pln"> TGROUP BY </span><span class="typ">CreateTime</span> |
其实行转列，关键是要理清逻辑，而且对分组（Group by）概念比较清晰。上面两个列子基本上就是行转列的类型了。但是有个问题来了，上面是我为了说明弄的一个简单列子。实际中，可能支付方式特别多，而且逻 辑也复杂很多，可能涉及汇率、手续费等等(曾经做个这样一个)，如果支付方式特别多，我们的CASE WHEN 会弄出一大堆，确实比较恼火，而且新增一种支付方式，我们还得修改脚本如果把上面的脚本用动态SQL改写一下，我们就能轻松解决这个问题
<span class="pln">DECLARE </span><span class="lit">@cmdText</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">8000</span><span class="pun">);</span><span class="pln">DECLARE </span><span class="lit">@tmpSql</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">8000</span><span class="pun">);</span><span class="pln">SET </span><span class="lit">@cmdText</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,'</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> CHAR</span><span class="pun">(</span><span class="lit">10</span><span class="pun">);</span><span class="pln">SELECT </span><span class="lit">@cmdText</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@cmdText</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">' CASE PayType WHEN '''</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="typ">PayType</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">''' THEN SUM(Money) ELSE 0 END AS '''</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="typ">PayType</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">''','</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> CHAR</span><span class="pun">(</span><span class="lit">10</span><span class="pun">)</span><span class="pln"> FROM </span><span class="pun">(</span><span class="pln">SELECT DISTINCT </span><span class="typ">PayType</span><span class="pln"> FROM </span><span class="typ">Inpours</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> TSET </span><span class="lit">@cmdText</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> LEFT</span><span class="pun">(</span><span class="lit">@cmdText</span><span class="pun">,</span><span class="pln"> LEN</span><span class="pun">(</span><span class="lit">@cmdText</span><span class="pun">)</span><span class="pln"> </span><span class="pun">-</span><span class="lit">2</span><span class="pun">)</span><span class="pln"> </span><span class="pun">--注意这里，如果没有加</span><span class="pln">CHAR</span><span class="pun">(</span><span class="lit">10</span><span class="pun">)</span><span class="pln"> </span><span class="pun">则用</span><span class="pln">LEFT</span><span class="pun">(</span><span class="lit">@cmdText</span><span class="pun">,</span><span class="pln"> LEN</span><span class="pun">(</span><span class="lit">@cmdText</span><span class="pun">)</span><span class="pln"> </span><span class="pun">-</span><span class="lit">1</span><span class="pun">)</span><span class="pln">SET </span><span class="lit">@cmdText</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@cmdText</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">' FROM Inpours GROUP BY CreateTime, PayType '</span><span class="pun">;</span><span class="pln">SET </span><span class="lit">@tmpSql</span><span class="pln"> </span><span class="pun">=</span><span class="str">'SELECT CreateTime,'</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> CHAR</span><span class="pun">(</span><span class="lit">10</span><span class="pun">);</span><span class="pln">SELECT </span><span class="lit">@tmpSql</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@tmpSql</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">' ISNULL(SUM('</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="typ">PayType</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">'), 0) AS '''</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="typ">PayType</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">''','</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> CHAR</span><span class="pun">(</span><span class="lit">10</span><span class="pun">)</span><span class="pln"> FROM </span><span class="pun">(</span><span class="pln">SELECT DISTINCT </span><span class="typ">PayType</span><span class="pln"> FROM </span><span class="typ">Inpours</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> TSET </span><span class="lit">@tmpSql</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> LEFT</span><span class="pun">(</span><span class="lit">@tmpSql</span><span class="pun">,</span><span class="pln"> LEN</span><span class="pun">(</span><span class="lit">@tmpSql</span><span class="pun">)</span><span class="pln"> </span><span class="pun">-</span><span class="lit">2</span><span class="pun">)</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">' FROM ('</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> CHAR</span><span class="pun">(</span><span class="lit">10</span><span class="pun">);</span><span class="pln">SET </span><span class="lit">@cmdText</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@tmpSql</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="lit">@cmdText</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">') T GROUP BY CreateTime '</span><span class="pun">;</span><span class="pln">PRINT </span><span class="lit">@cmdText</span><span class="pln">EXECUTE </span><span class="pun">(</span><span class="lit">@cmdText</span><span class="pun">);</span> |
下面是通过PIVOT来进行行转列的用法，大家可以对比一下，确实要简单、更具可读性（呵呵，习惯的前提下）
<span class="pln">SELECT </span><span class="typ">CreateTime</span><span class="pun">,</span><span class="pln"> </span><span class="pun">[支付宝]</span><span class="pln"> </span><span class="pun">,</span><span class="pln"> </span><span class="pun">[手机短信],</span><span class="pln"> </span><span class="pun">[工商银行卡]</span><span class="pln"> </span><span class="pun">,</span><span class="pln"> </span><span class="pun">[建设银行卡]</span><span class="pln">FROM</span><span class="pun">(</span><span class="pln"> SELECT CONVERT</span><span class="pun">(</span><span class="pln">VARCHAR</span><span class="pun">(</span><span class="lit">10</span><span class="pun">),</span><span class="pln"> </span><span class="typ">CreateTime</span><span class="pun">,</span><span class="pln"> </span><span class="lit">120</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">CreateTime</span><span class="pun">,</span><span class="typ">PayType</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Money</span><span class="pln"> FROM </span><span class="typ">Inpours</span><span class="pln"></span><span class="pun">)</span><span class="pln"> PPIVOT </span><span class="pun">(</span><span class="pln"> SUM</span><span class="pun">(</span><span class="typ">Money</span><span class="pun">)</span><span class="pln"> FOR </span><span class="typ">PayType</span><span class="pln"> IN </span><span class="pun">([支付宝],</span><span class="pln"> </span><span class="pun">[手机短信],</span><span class="pln"> </span><span class="pun">[工商银行卡],</span><span class="pln"> </span><span class="pun">[建设银行卡])</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> AS TORDER BY </span><span class="typ">CreateTime</span> |
有时可能会出现这样的错误：
消息 325，级别 15，状态 1，第 9 行
‘PIVOT’ 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值，以启用此功能。有关存储过程 sp_dbcmptlevel 的信息，请参见帮助。
这个是因为：对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时，必须将数据库的兼容级别设置为 90 或更高。有关如何设置数据库兼容级别的信息，请参阅 sp_dbcmptlevel (Transact-SQL)。 例如，只需在执行上面脚本前加上 EXEC sp_dbcmptlevel Test, 90; 就OK了， Test 是所在数据库的名称。
下面我们来看看列转行，主要是通过UNION ALL ,MAX来实现。假如有下面这么一个表
<span class="pln">CREATE TABLE </span><span class="typ">ProgrectDetail</span><span class="pln"></span><span class="pun">(</span><span class="pln"> </span><span class="typ">ProgrectName</span><span class="pln"> NVARCHAR</span><span class="pun">(</span><span class="lit">20</span><span class="pun">),</span><span class="pln"> </span><span class="pun">--工程名称</span><span class="pln"> </span><span class="typ">OverseaSupply</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln"> </span><span class="pun">--海外供应商供给数量</span><span class="pln"> </span><span class="typ">NativeSupply</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln"> </span><span class="pun">--国内供应商供给数量</span><span class="pln"> </span><span class="typ">SouthSupply</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln"> </span><span class="pun">--南方供应商供给数量</span><span class="pln"> </span><span class="typ">NorthSupply</span><span class="pln"> INT </span><span class="pun">--北方供应商供给数量</span><span class="pln"></span><span class="pun">)</span><span class="pln">INSERT INTO </span><span class="typ">ProgrectDetail</span><span class="pln">SELECT </span><span class="str">'A'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">100</span><span class="pun">,</span><span class="pln"> </span><span class="lit">200</span><span class="pun">,</span><span class="pln"> </span><span class="lit">50</span><span class="pun">,</span><span class="pln"> </span><span class="lit">50</span><span class="pln">UNION ALLSELECT </span><span class="str">'B'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">200</span><span class="pun">,</span><span class="pln"> </span><span class="lit">300</span><span class="pun">,</span><span class="pln"> </span><span class="lit">150</span><span class="pun">,</span><span class="pln"> </span><span class="lit">150</span><span class="pln">UNION ALLSELECT </span><span class="str">'C'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">159</span><span class="pun">,</span><span class="pln"> </span><span class="lit">400</span><span class="pun">,</span><span class="pln"> </span><span class="lit">20</span><span class="pun">,</span><span class="pln"> </span><span class="lit">320</span><span class="pln">UNION ALLSELECT </span><span class="str">'D'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">250</span><span class="pun">,</span><span class="pln"> </span><span class="lit">30</span><span class="pun">,</span><span class="pln"> </span><span class="lit">15</span><span class="pun">,</span><span class="pln"> </span><span class="lit">15</span> |
我们可以通过下面的脚本来实现，查询结果如下图所示
<span class="pln">SELECT </span><span class="typ">ProgrectName</span><span class="pun">,</span><span class="pln"> </span><span class="str">'OverseaSupply'</span><span class="pln"> AS </span><span class="typ">Supplier</span><span class="pun">,</span><span class="pln"> MAX</span><span class="pun">(</span><span class="typ">OverseaSupply</span><span class="pun">)</span><span class="pln"> AS </span><span class="str">'SupplyNum'</span><span class="pln">FROM </span><span class="typ">ProgrectDetail</span><span class="pln">GROUP BY </span><span class="typ">ProgrectName</span><span class="pln">UNION ALLSELECT </span><span class="typ">ProgrectName</span><span class="pun">,</span><span class="pln"> </span><span class="str">'NativeSupply'</span><span class="pln"> AS </span><span class="typ">Supplier</span><span class="pun">,</span><span class="pln"> MAX</span><span class="pun">(</span><span class="typ">NativeSupply</span><span class="pun">)</span><span class="pln"> AS </span><span class="str">'SupplyNum'</span><span class="pln">FROM </span><span class="typ">ProgrectDetail</span><span class="pln">GROUP BY </span><span class="typ">ProgrectName</span><span class="pln">UNION ALLSELECT </span><span class="typ">ProgrectName</span><span class="pun">,</span><span class="pln"> </span><span class="str">'SouthSupply'</span><span class="pln"> AS </span><span class="typ">Supplier</span><span class="pun">,</span><span class="pln"> MAX</span><span class="pun">(</span><span class="typ">SouthSupply</span><span class="pun">)</span><span class="pln"> AS </span><span class="str">'SupplyNum'</span><span class="pln">FROM </span><span class="typ">ProgrectDetail</span><span class="pln">GROUP BY </span><span class="typ">ProgrectName</span><span class="pln">UNION ALLSELECT </span><span class="typ">ProgrectName</span><span class="pun">,</span><span class="pln"> </span><span class="str">'NorthSupply'</span><span class="pln"> AS </span><span class="typ">Supplier</span><span class="pun">,</span><span class="pln"> MAX</span><span class="pun">(</span><span class="typ">NorthSupply</span><span class="pun">)</span><span class="pln"> AS </span><span class="str">'SupplyNum'</span><span class="pln">FROM </span><span class="typ">ProgrectDetail</span><span class="pln">GROUP BY </span><span class="typ">ProgrectName</span> |
用UNPIVOT 实现如下：
<span class="pln">SELECT </span><span class="typ">ProgrectName</span><span class="pun">,</span><span class="typ">Supplier</span><span class="pun">,</span><span class="typ">SupplyNum</span><span class="pln">FROM</span><span class="pun">(</span><span class="pln"> SELECT </span><span class="typ">ProgrectName</span><span class="pun">,</span><span class="pln"> </span><span class="typ">OverseaSupply</span><span class="pun">,</span><span class="pln"> </span><span class="typ">NativeSupply</span><span class="pun">,</span><span class="pln"> </span><span class="typ">SouthSupply</span><span class="pun">,</span><span class="pln"> </span><span class="typ">NorthSupply</span><span class="pln"> FROM </span><span class="typ">ProgrectDetail</span><span class="pln"></span><span class="pun">)</span><span class="pln">TUNPIVOT </span><span class="pun">(</span><span class="pln"> </span><span class="typ">SupplyNum</span><span class="pln"> FOR </span><span class="typ">Supplier</span><span class="pln"> IN </span><span class="pun">(</span><span class="typ">OverseaSupply</span><span class="pun">,</span><span class="pln"> </span><span class="typ">NativeSupply</span><span class="pun">,</span><span class="pln"> </span><span class="typ">SouthSupply</span><span class="pun">,</span><span class="pln"> </span><span class="typ">NorthSupply</span><span class="pln"> </span><span class="pun">)</span><span class="pln"></span><span class="pun">)</span><span class="pln"> P</span> |
