sqlserver 行转列

还写了一篇 Linq 实现 DataTable 行转列 有时间大家可以看一下 sqlserver把行转成列在我们编码中是经常遇到的我做一个小例子大家看一下 1 -- 创建一个表 2 create table PayPhoneMoney 3 ( 4 id int identity ( 1 , 1 ), 5 userName Nvarchar ( 20 ), 6 payT

还写了一篇Linq 实现 DataTable 行转列有时间大家可以看一下

sqlserver把行转成列在我们编码中是经常遇到的我做一个小例子大家看一下

<span> 1</span> <span>--</span><span>创建一个表</span><span><br></span><span> 2</span> <span>create</span> <span>table</span> PayPhoneMoney<br><span> 3</span> (<br><span> 4</span>     id <span>int</span> <span>identity</span>(<span>1</span>,<span>1</span>),<br><span> 5</span>     userName <span>Nvarchar</span>(<span>20</span>),<br><span> 6</span>     payType <span>nvarchar</span>(<span>20</span>),<br><span> 7</span>     <span>money</span>   <span>decimal</span>,<br><span> 8</span>     payTime <span>datetime</span>,<br><span> 9</span>     <span>constraint</span> pk_id <span>primary</span> <span>key</span>(id)<br><span>10</span> )<br><span>11</span> <span>--</span><span>插入点数据</span><span><br></span><span>12</span> <span>insert</span> <span>into</span> PayPhoneMoney <span>values</span>(<span>'</span><span>小李</span><span>'</span>,<span>'</span><span>支付宝</span><span>'</span>,<span>20</span>,<span>'</span><span>2012-01-03</span><span>'</span>)<br><span>13</span> <span>insert</span> <span>into</span> PayPhoneMoney <span>values</span>(<span>'</span><span>小陈</span><span>'</span>,<span>'</span><span>工行</span><span>'</span>,<span>20</span>,<span>'</span><span>2012-01-06</span><span>'</span>)<br><span>14</span> <span>insert</span> <span>into</span> PayPhoneMoney <span>values</span>(<span>'</span><span>小赵</span><span>'</span>,<span>'</span><span>交行</span><span>'</span>,<span>50</span>,<span>'</span><span>2012-01-06</span><span>'</span>)<br><span>15</span> <span>insert</span> <span>into</span> PayPhoneMoney <span>values</span>(<span>'</span><span>小陈</span><span>'</span>,<span>'</span><span>支付宝</span><span>'</span>,<span>60</span>,<span>'</span><span>2012-01-06</span><span>'</span>)<br><span>16</span> <span>insert</span> <span>into</span> PayPhoneMoney <span>values</span>(<span>'</span><span>小赵</span><span>'</span>,<span>'</span><span>工行</span><span>'</span>,<span>30</span>,<span>'</span><span>2012-01-16</span><span>'</span>)<br><span>17</span> <span>insert</span> <span>into</span> PayPhoneMoney <span>values</span>(<span>'</span><span>小张</span><span>'</span>,<span>'</span><span>中行</span><span>'</span>,<span>30</span>,<span>'</span><span>2012-01-16</span><span>'</span>)<br><span>18</span> <span>insert</span> <span>into</span> PayPhoneMoney <span>values</span>(<span>'</span><span>小李</span><span>'</span>,<span>'</span><span>支付宝</span><span>'</span>,<span>60</span>,<span>'</span><span>2012-01-16</span><span>'</span>)

看一下表中的数据

我们要想查一下每个人所有支付形式下的总钱数如图所示

<span>1</span> <span>--</span><span> 查一下每个人所有支付形式下的总钱数</span><span><br></span><span>2</span> <span>select</span> userName <span>from</span> PayPhoneMoney <span>group</span> <span>by</span> userName<br><span>3</span> <span>select</span> userName,<br><span>4</span>     <span>sum</span>(<span>case</span> payType <span>when</span> <span>'</span><span>支付宝</span><span>'</span> <span>then</span> <span>money</span> <span>else</span> <span>0</span> <span>end</span>) <span>as</span> 支付宝 ,<br><span>5</span>     <span>sum</span>(<span>case</span> payType <span>when</span> <span>'</span><span>工行</span><span>'</span> <span>then</span> <span>money</span> <span>else</span> <span>0</span> <span>end</span>) <span>as</span> 工行, <br><span>6</span>     <span>sum</span>(<span>case</span> payType <span>when</span> <span>'</span><span>交行</span><span>'</span> <span>then</span> <span>money</span> <span>else</span> <span>0</span> <span>end</span>) <span>as</span> 交行,<br><span>7</span>     <span>sum</span>(<span>case</span> payType <span>when</span> <span>'</span><span>中行</span><span>'</span> <span>then</span> <span>money</span> <span>else</span> <span>0</span> <span>end</span>) <span>as</span> 中行<br><span>8</span> <span>from</span> PayPhoneMoney <br><span>9</span> <span>group</span> <span>by</span> userName

--我们这只列出了几种支付方式实际中还有很多支付方式不能一个一个都用case when 吧
--可以这样

<span>1</span> <span>declare</span> <span>@cmdText</span> <span>varchar</span>(<span>8000</span>)<br><span>2</span> <span>set</span> <span>@cmdText</span><span>=</span><span>'</span><span>select userName, </span><span>'</span><br><span>3</span> <span>select</span> <span>@cmdText</span><span>=</span><span>@cmdText</span><span>+</span><span>'</span><span> sum(case payType when</span><span>'''</span><span>+</span>payType<span>+</span><span>'''</span><span>Then money else 0 end) as </span><span>'''</span><span>+</span>payType<br><span>4</span> <span>+</span><span>'''</span><span>,</span><span>'</span><span>+</span><span>char</span>(<span>10</span>) <span>from</span> (<span>select</span> <span>Distinct</span> payType <span>from</span> PayPhoneMoney) T<br><span>5</span> <span>print</span> <span>@cmdText</span><span>--</span><span>发现多一个逗号下面把逗号去掉</span><span><br></span><span>6</span> <span>set</span> <span>@cmdText</span><span>=left</span>(<span>@cmdText</span>,<span>len</span>(<span>@cmdText</span>)<span>-</span><span>2</span>)<span>--</span><span>去掉逗号</span><span><br></span><span>7</span> <span>set</span> <span>@cmdText</span><span>=</span><span>@cmdText</span><span>+</span><span>'</span><span>from PayPhoneMoney group by userName</span><span>'</span><br><span>8</span> <span>print</span> <span>@cmdText</span><br><span>9</span> <span>exec</span>(<span>@cmdText</span>)

看一下结果是一样的吧