SqlServer 列转换为行，字段字符串相连代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 SqlServer 列转换为行，字段字符串相连
作者:Dezai.CN / 发布于2011/8/7/ 770
<div>
<pre><span style="color: #0000ff">DECLARE</span><span style="color: #000000"> </span><span style="color: #008000">@T1</span><span style="color: #000000"> </span><span style="color: #0000ff">table</span><span style="color: #000000">(UserID </span><span style="color: #0000ff">int</span><span style="color: #000000"> , UserName </span><span style="color: #0000ff">nvarchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">50</span><span style="color: #000000">),CityName </span><span style="color: #0000ff">nvarchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">50</span><span style="color: #000000">));  </span><span style="color: #0000ff">insert</span><span style="color: #000000"> </span><span style="color: #0000ff">into</span><span style="color: #000000"> </span><span style="color: #008000">@T1</span><span style="color: #000000"> (UserID,UserName,CityName) </span><span style="color: #0000ff">values</span><span style="color: #000000"> (</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">a</span><span style="color: #ff0000">'</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">上海</span><span style="color: #ff0000">'</span><span style="color: #000000">)  </span><span style="color: #0000ff">insert</span><span style="color: #000000"> </span><span style="color: #0000ff">into</span><span style="color: #000000"> </span><span style="color: #008000">@T1</span><span style="color: #000000"> (UserID,UserName,CityName) </span><span style="color: #0000ff">values</span><span style="color: #000000"> (</span><span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">b</span><span style="color: #ff0000">'</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">北京</span><span style="color: #ff0000">'</span><span style="color: #000000">)  </span><span style="color: #0000ff">insert</span><span style="color: #000000"> </span><span style="color: #0000ff">into</span><span style="color: #000000"> </span><span style="color: #008000">@T1</span><span style="color: #000000"> (UserID,UserName,CityName) </span><span style="color: #0000ff">values</span><span style="color: #000000"> (</span><span style="color: #800000; font-weight: bold">3</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">c</span><span style="color: #ff0000">'</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">上海</span><span style="color: #ff0000">'</span><span style="color: #000000">)  </span><span style="color: #0000ff">insert</span><span style="color: #000000"> </span><span style="color: #0000ff">into</span><span style="color: #000000"> </span><span style="color: #008000">@T1</span><span style="color: #000000"> (UserID,UserName,CityName) </span><span style="color: #0000ff">values</span><span style="color: #000000"> (</span><span style="color: #800000; font-weight: bold">4</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">d</span><span style="color: #ff0000">'</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">北京</span><span style="color: #ff0000">'</span><span style="color: #000000">)  </span><span style="color: #0000ff">insert</span><span style="color: #000000"> </span><span style="color: #0000ff">into</span><span style="color: #000000"> </span><span style="color: #008000">@T1</span><span style="color: #000000"> (UserID,UserName,CityName) </span><span style="color: #0000ff">values</span><span style="color: #000000"> (</span><span style="color: #800000; font-weight: bold">5</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">e</span><span style="color: #ff0000">'</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">上海</span><span style="color: #ff0000">'</span><span style="color: #000000">) </span><span style="color: #008080">--</span><span style="color: #008080">select a.UserName from @T1 a</span><span style="color: #008080"></span><span style="color: #0000ff">select</span><span style="color: #000000"> a.UserName </span><span style="color: #0000ff">from</span><span style="color: #000000"> </span><span style="color: #008000">@T1</span><span style="color: #000000"> a </span><span style="color: #0000ff">for</span><span style="color: #000000"> xml path(</span><span style="color: #ff0000">'</span><span style="color: #ff0000">row</span><span style="color: #ff0000">'</span><span style="color: #000000">)</span><span style="color: #008080">--</span><span style="color: #008080">select cityname,(select a.UserName from @T1 a where a.CityName=t.CityName for xml path('row')) from @T1 t group by CityName</span><span style="color: #008080"></span><span style="color: #000000"></span><span style="color: #008080">--</span><span style="color: #008080">select cityname,replace</span><span style="color: #008080">--</span><span style="color: #008080">((select a.UserName from @T1 a where a.CityName=t.CityName for xml path('row'))</span><span style="color: #008080">--</span><span style="color: #008080">,'<row><UserName>','')</span><span style="color: #008080">--</span><span style="color: #008080"> from @T1 t group by CityName</span><span style="color: #008080"></span><span style="color: #000000"></span><span style="color: #008080">--</span><span style="color: #008080">第一种方法：</span><span style="color: #008080"></span><span style="color: #0000ff">select</span><span style="color: #000000"> cityname,</span><span style="color: #ff00ff">REPLACE</span><span style="color: #000000">(</span><span style="color: #ff00ff">replace</span><span style="color: #000000">((</span><span style="color: #0000ff">select</span><span style="color: #000000"> a.UserName </span><span style="color: #0000ff">from</span><span style="color: #000000"> </span><span style="color: #008000">@T1</span><span style="color: #000000"> a </span><span style="color: #0000ff">where</span><span style="color: #000000"> a.CityName</span><span style="color: #808080">=</span><span style="color: #000000">t.CityName </span><span style="color: #0000ff">for</span><span style="color: #000000"> xml path(</span><span style="color: #ff0000">'</span><span style="color: #ff0000">row</span><span style="color: #ff0000">'</span><span style="color: #000000">)),</span><span style="color: #ff0000">'</span><span style="color: #ff0000"><row><UserName></span><span style="color: #ff0000">'</span><span style="color: #000000">,</span><span style="color: #ff0000">''</span><span style="color: #000000">),</span><span style="color: #ff0000">'</span><span style="color: #ff0000"></UserName></row></span><span style="color: #ff0000">'</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">,</span><span style="color: #ff0000">'</span><span style="color: #000000">) </span><span style="color: #0000ff">from</span><span style="color: #000000"> </span><span style="color: #008000">@T1</span><span style="color: #000000"> t </span><span style="color: #0000ff">group</span><span style="color: #000000"> </span><span style="color: #0000ff">by</span><span style="color: #000000"> CityName</span><span style="color: #008080">--</span><span style="color: #008080">第二种方法：</span><span style="color: #008080"></span><span style="color: #0000ff">SELECT</span><span style="color: #000000"> B.CityName,</span><span style="color: #808080">LEFT</span><span style="color: #000000">(UserList,</span><span style="color: #ff00ff">LEN</span><span style="color: #000000">(UserList)</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">) </span><span style="color: #0000ff">FROM</span><span style="color: #000000"> (  </span><span style="color: #0000ff">SELECT</span><span style="color: #000000"> CityName,  (</span><span style="color: #0000ff">SELECT</span><span style="color: #000000"> UserName</span><span style="color: #808080">+</span><span style="color: #ff0000">'</span><span style="color: #ff0000">,</span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #0000ff">FROM</span><span style="color: #000000"> </span><span style="color: #008000">@T1</span><span style="color: #000000"> </span><span style="color: #0000ff">WHERE</span><span style="color: #000000"> CityName</span><span style="color: #808080">=</span><span style="color: #000000">A.CityName  </span><span style="color: #0000ff">FOR</span><span style="color: #000000"> XML PATH(</span><span style="color: #ff0000">''</span><span style="color: #000000">)) </span><span style="color: #0000ff">AS</span><span style="color: #000000"> UserList   </span><span style="color: #0000ff">FROM</span><span style="color: #000000"> </span><span style="color: #008000">@T1</span><span style="color: #000000"> A   </span><span style="color: #0000ff">GROUP</span><span style="color: #000000"> </span><span style="color: #0000ff">BY</span><span style="color: #000000"> CityName  ) B </span></pre>
</div>
试试其它关键字
　列转换为行　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)