多列转1列 SqlServer 实现oracle10g的 wmsys.wm

有这么一个表 create table tb(id int , value varchar ( 10 )) insert into tb values ( 1 , ' aa ' ) insert into tb values ( 1 , ' bb ' ) insert into tb values ( 2 , ' aaa ' ) insert into tb values ( 2 , ' bbb ' ) insert into tb values ( 2 , '

有这么一个表 

<span>create</span> <span>table</span> tb(id <span>int</span>, value <span>varchar</span>(<span>10</span><span>))  
</span><span>insert</span> <span>into</span> tb <span>values</span>(<span>1</span>, <span>'</span><span>aa</span><span>'</span><span>)  
</span><span>insert</span> <span>into</span> tb <span>values</span>(<span>1</span>, <span>'</span><span>bb</span><span>'</span><span>)  
</span><span>insert</span> <span>into</span> tb <span>values</span>(<span>2</span>, <span>'</span><span>aaa</span><span>'</span><span>)  
</span><span>insert</span> <span>into</span> tb <span>values</span>(<span>2</span>, <span>'</span><span>bbb</span><span>'</span><span>)  
</span><span>insert</span> <span>into</span> tb <span>values</span>(<span>2</span>, <span>'</span><span>ccc</span><span>'</span><span>)  
</span><span>insert</span> <span>into</span> tb <span>values</span>(<span>3</span>, <span>'</span><span>ddd</span><span>'</span><span>)  
</span><span>insert</span> <span>into</span> tb <span>values</span>(<span>3</span>, <span>'</span><span>fff</span><span>'</span>) 

                                                                                                                             

 

需求：把id为3所以数据转为1行。以|分隔

 

1.把id为3的数据转  xml

 

xml

 

select '|'+(value) from tb

where id=3

for xml path('')

效果

 

 

 

2. 去掉第一个  | 替换成空格  

 

需要函数 Stuff,,   类似对字符串操作这样的函数还有 replace() right () left() substring() 看需求使用

 

STUFF 函数将字符串插入另一字符串。它在第一个字符串中从开始位置删除指定长度的字符；然后将第二个字符串插入第一个字符串的开始位置。

 Transact-SQL 语法约定

语法

<span>STUFF</span> <strong>(</strong> <em>character_expression </em><strong>, </strong><em>start </em><strong>, </strong><em>length </em><strong>,</strong><em>character_expression </em><strong>)</strong>

 

stuff((select '|'+(value) from tb

where id=3

for xml path('')),1,1,'')  

这句无法单独运行

 

3.最终实现

 

<span>select</span> id ,value<span>=</span><span>stuff</span>((<span>select</span> <span>'</span><span>|</span><span>'</span><span>+</span>(value) <span>from</span><span> tb 
</span><span>where</span> id<span>=</span><span>3</span>
<span>for</span> xml path(<span>''</span>)),<span>1</span>,<span>1</span>,<span>''</span>)  <span>from</span> tb <span>where</span> id<span>=</span><span>3</span> <span>group</span> <span>by</span> id

ps:去掉一个where 得到的结果是不一样的，不懂为何。group by id 有去重的效果

 

效果图。没有------------

在oracle 数据库中他也有自己的实现。觉得这些东西没有什么的。只要你有需求，必然有答案，只是你接触了没有而已。反正现在编程我们都是站在巨人的肩膀上了。