sql server 使用for xml path 将1列多行转换为字符串连接起来

时间:2021-11-14 04:20:40
Sql代码    sql server 使用for xml path 将1列多行转换为字符串连接起来
  1. create table tb ([id] int,[namevarchar(2))  
  2. insert into tb  
  3. select 1,'aa' union all  
  4. select 2,'bb' union all  
  5. select 1,'cc' union all  
  6. select 3,'dd' union all  
  7. select 2,'ee'  
  8.    
  9. select * from tb  
  10. --查询  
  11. select   
  12.   id,  
  13.   name=stuff((select ','+name from tb where id=t.id for xml path('')),1,1,'')  
  14. from  
  15.   tb t  
  16. group by  
  17.   id  
  18.    
  19.    
  20.    
  21. 方法二:  
  22.    
  23. CREATE FUNCTION GET_STRING(@ID INT)  
  24. RETURNS VARCHAR(50)  
  25. AS   
  26.   BEGIN   
  27.        DECLARE @NAME VARCHAR(500)  
  28.        SELECT @NAME=ISNULL(@NAME+',','')+NAME FROM TB WHERE ID=@ID  
  29.        RETURN @NAME  
  30.   END  
  31.   
  32.   
  33. SELECT ID ,DBO.GET_STRING(ID)NAME FROM TB GROUP BY ID  
  34.   
  35. -----------------------------------------------------------------------  
  36. 在oracle中,是有相对应的函数,sys_connect_by_path函数。  
  37.   
  38. 先在这里把oracle的函数实现也写下吧  
  39.   
  40. select id  
  41.       ,itrim(max(sys_connect_by_path(name,','))) as name  
  42. from   
  43.     (select id  
  44.     ,name  
  45.     ,lead(rnFirst) over(partition by no order by rnFirst)rnNext  
  46.      from   
  47.   (select a.id  
  48.   ,a.name  
  49.   ,row_number() over(order by a.id,a.name desc) rnFirst  
  50.    from @t a) tmpTable1  
  51. ) tmpTable2  
  52. start with rnNext is null  
  53. connect by rnNext = PRior rnFirst  
  54. group by id  
  55.   
  56. 以上是实现oracle中的行转列方式。  
  57.   
  58. ********************************************下面我们来看一下sqlserver的,因为sqlserver没提供类似的函数,不知道sqlserver2012提供没  
  59.   
  60. 不过sqlserver支持起来也很简单,使用cross apply和for xml path组合来用,这两个单独都知道是怎么回事,怎么使用,但是真不知道还有它俩组合来完成这个功能的,感到很是不可思议,这里要感谢下网络上的人,原来自己写东西都是把所学到的知识记录到本机,从不放到网上,也可能是最近吧,好多工作不会,都是通过网络帮助,就把我所知道的东西也都放到网上来,大家一起学习。  
  61.   
  62. sqlserver:  
  63.   
  64. select id, SUBSTRING(names, 1, len(names)-1) from @t AS A cross APPLY  
  65. (SELECT NAME + ',' FROM @t AS B WHERE A.id = B.id FOR XML PATH('')) D (names)  
  66.   
  67. GROUP BY id, names