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

<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>)