SQL把ID相同的记录合并成同一条记录代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 SQL把ID相同的记录合并成同一条记录
作者:Dezai.CN / 发布于2011/9/16/ 986
<div><span style="color: rgb(0,0,255)">DECLARE</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@Table</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">TABLE</span><span style="color: rgb(0,0,0)">(id </span><span style="color: rgb(0,0,255)">int</span><span style="color: rgb(0,0,0)">, value </span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">10</span><span style="color: rgb(0,0,0)">))
</span><span style="color: rgb(0,0,255)">INSERT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@Table</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">AA</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">UNION</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">ALL</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">BB</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">UNION</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">ALL</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">2</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">AAA</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">UNION</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">ALL</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">2</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">BBB</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">UNION</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">ALL</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">2</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">CCC</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">UNION</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">ALL</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">3</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">AAAA</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">UNION</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">ALL</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">3</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">BBBB</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">UNION</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">ALL</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">3</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">XXXX</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">

</span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">*</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">FROM</span><span style="color: rgb(0,0,0)">(
</span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">DISTINCT</span><span style="color: rgb(0,0,0)"> 
id
</span><span style="color: rgb(0,0,255)">FROM</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@Table</span><span style="color: rgb(0,0,0)">
) A
</span><span style="color: rgb(128,128,128)">OUTER</span><span style="color: rgb(0,0,0)"> APPLY(
</span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">values</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">STUFF</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">REPLACE</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">REPLACE</span><span style="color: rgb(0,0,0)">(
(
</span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> value </span><span style="color: rgb(0,0,255)">FROM</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@Table</span><span style="color: rgb(0,0,0)"> N
</span><span style="color: rgb(0,0,255)">WHERE</span><span style="color: rgb(0,0,0)"> id </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> A.id
</span><span style="color: rgb(0,0,255)">FOR</span><span style="color: rgb(0,0,0)"> XML AUTO
), </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)"><N value="</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">), </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">"/></span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(0,0,0)">), </span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(0,0,0)">)
) N</span></div>
试试其它关键字
　相同的记录合并　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)