多行记录拼合成字符串

最新推荐文章于 2021-08-01 20:56:22 发布

不停歇的步伐

最新推荐文章于 2021-08-01 20:56:22 发布

阅读量685

点赞数

分类专栏： SQLSERVER 文章标签： function

本文链接：https://blog.csdn.net/zyyjc/article/details/5068938

版权

SQLSERVER 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

ALTER FUNCTION [dbo].[GetCity_Name] (@AreaID int)
--传入区域ID，返回对应的城市拼合字符串

RETURNS   varchar(200)
BEGIN

declare   @s   varchar(8000)
set   @s=’’
select   @s=@s+’/’+CityName   from
(select CityName from City WHERE AreaId=@AreaId)   a

set   @s=   stuff(@s,1,1,’’)

    RETURN(@s)
END

--生成测试数据 
 create table 表(部门 int,人员 varchar(20)) 
 insert into 表 select 1,'张三' 
 insert into 表 select 1,'李四' 
 insert into 表 select 1,'王五' 
 insert into 表 select 2,'赵六' 
 insert into 表 select 2,'邓七' 
 insert into 表 select 2,'刘八' 
 go 
  
 --创建用户定义函数 
 create function f_str(@department int) 
 returns varchar(8000) 
 as 
 begin 
     declare @ret varchar(8000) 
     set @ret = '' 
     select @ret = @ret+','+人员 from 表 where 部门 = @department 
     set @ret = stuff(@ret,1,1,'') 
     return @ret  
 end 
 go 
  
  
 --执行 
 select 部门,人员=dbo.f_str(部门) from 表 group by 部门 order by 部门 
 go 
  
 --输出结果 
 /* 
 部门  人员 
 ----  -------------- 
 1     张三,李四,王五 
 2     赵六,邓七,刘八 
 */ 
  
  
 --删除测试数据 
 drop function f_str 
 drop table 表 
 go 
  
  
 SQL code问题描述： 
 无论是在sql 2000，还是在 sql 2005 中,都没有提供字符串的聚合函数， 
   所以，当我们在处理下列要求时，会比较麻烦： 
 有表tb, 如下： 
 id    value 
 ----- ------ 
 1     aa 
 1     bb 
 2     aaa 
 2     bbb 
 2     ccc 
 需要得到结果： 
 id     values 
 ------ ----------- 
 1      aa,bb 
 2      aaa,bbb,ccc 
 即， group by id, 求 value 的和（字符串相加） 
  
 1. 旧的解决方法 
  
 -- 1. 创建处理函数 
 CREATE FUNCTION dbo.f_str(@id int) 
 RETURNS varchar(8000) 
 AS 
 BEGIN 
     DECLARE @r varchar(8000) 
     SET @r = '' 
     SELECT @r = @r + ',' + value 
     FROM tb 
     WHERE id=@id 
     RETURN STUFF(@r, 1, 1, '') 
 END 
 GO 
 -- 调用函数 
  
 SELECt id, values=dbo.f_str(id)  
 FROM tb  
 GROUP BY id 
  
 -- 2. 新的解决方法  
 -- 示例数据 
 DECLARE @t TABLE(id int, value varchar(10)) 
 INSERT @t SELECT 1, 'aa' 
 UNION ALL SELECT 1, 'bb' 
 UNION ALL SELECT 2, 'aaa' 
 UNION ALL SELECT 2, 'bbb' 
 UNION ALL SELECT 2, 'ccc' 
  
 -- 查询处理 
 SELECT * 
 FROM( 
     SELECT DISTINCT  
         id 
     FROM @t 
 )A 
 OUTER APPLY( 
     SELECT  
         [values]= STUFF(REPLACE(REPLACE( 
             ( 
                 SELECT value FROM @t N 
                 WHERE id = A.id 
                 FOR XML AUTO 
             ), ' <N value="', ','), '"/>', ''), 1, 1, '') 
 )N 
  
 /*--结果 
 id          values 
 ----------- ---------------- 
 1           aa,bb 
 2           aaa,bbb,ccc 
 (2 行受影响) 
 --*/ 
  
 --各种字符串分函数 
  
 --3.3.1 使用游标法进行字符串合并处理的示例。 
 --处理的数据 
 CREATE TABLE tb(col1 varchar(10),col2 int) 
 INSERT tb SELECT 'a',1 
 UNION ALL SELECT 'a',2 
 UNION ALL SELECT 'b',1 
 UNION ALL SELECT 'b',2 
 UNION ALL SELECT 'b',3 
  
 --合并处理 
 --定义结果集表变量 
 DECLARE @t TABLE(col1 varchar(10),col2 varchar(100)) 
  
 --定义游标并进行合并处理 
 DECLARE tb CURSOR LOCAL 
 FOR 
 SELECT col1,col2 FROM tb ORDER BY  col1,col2 
 DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100) 
 OPEN tb 
 FETCH tb INTO @col1,@col2 
 SELECT @col1_old=@col1,@s='' 
 WHILE @@FETCH_STATUS=0 
 BEGIN 
     IF @col1=@col1_old 
         SELECT @s=@s+','+CAST(@col2 as varchar) 
     ELSE 
     BEGIN 
         INSERT @t VALUES(@col1_old,STUFF(@s,1,1,'')) 
         SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1 
     END 
     FETCH tb INTO @col1,@col2 
 END 
 INSERT @t VALUES(@col1_old,STUFF(@s,1,1,'')) 
 CLOSE tb 
 DEALLOCATE tb 
 --显示结果并删除测试数据 
 SELECT * FROM @t 
 DROP TABLE tb 
 /*--结果 
 col1       col2 
 ---------- ----------- 
 a          1,2 
 b          1,2,3 
 --*/ 
 GO 
  
  
 /*==============================================*/ 
  
  
 --3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例 
 --处理的数据 
 CREATE TABLE tb(col1 varchar(10),col2 int) 
 INSERT tb SELECT 'a',1 
 UNION ALL SELECT 'a',2 
 UNION ALL SELECT 'b',1 
 UNION ALL SELECT 'b',2 
 UNION ALL SELECT 'b',3 
 GO 
  
 --合并处理函数 
 CREATE FUNCTION dbo.f_str(@col1 varchar(10)) 
 RETURNS varchar(100) 
 AS 
 BEGIN 
     DECLARE @re varchar(100) 
     SET @re='' 
     SELECT @re=@re+','+CAST(col2 as varchar) 
     FROM tb 
     WHERE col1=@col1 
     RETURN(STUFF(@re,1,1,'')) 
 END 
 GO 
  
 --调用函数 
 SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1 
 --删除测试 
 DROP TABLE tb 
 DROP FUNCTION f_str 
 /*--结果 
 col1       col2 
 ---------- ----------- 
 a          1,2 
 b          1,2,3 
 --*/ 
 GO 
  
 /*==============================================*/ 
  
  
 --3.3.3 使用临时表实现字符串合并处理的示例 
 --处理的数据 
 CREATE TABLE tb(col1 varchar(10),col2 int) 
 INSERT tb SELECT 'a',1 
 UNION ALL SELECT 'a',2 
 UNION ALL SELECT 'b',1 
 UNION ALL SELECT 'b',2 
 UNION ALL SELECT 'b',3 
  
 --合并处理 
 SELECT col1,col2=CAST(col2 as varchar(100))  
 INTO #t FROM tb 
 ORDER BY col1,col2 
 DECLARE @col1 varchar(10),@col2 varchar(100) 
 UPDATE #t SET  
     @col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END, 
     @col1=col1, 
     col2=@col2 
 SELECT * FROM #t 
 /*--更新处理后的临时表 
 col1       col2 
 ---------- ------------- 
 a          1 
 a          1,2 
 b          1 
 b          1,2 
 b          1,2,3 
 --*/ 
 --得到最终结果 
 SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1 
 /*--结果 
 col1       col2 
 ---------- ----------- 
 a          1,2 
 b          1,2,3 
 --*/ 
 --删除测试 
 DROP TABLE tb,#t 
 GO 
  
  
 /*==============================================*/ 
  
 --3.3.4.1 每组  <=2 条记录的合并 
 --处理的数据 
 CREATE TABLE tb(col1 varchar(10),col2 int) 
 INSERT tb SELECT 'a',1 
 UNION ALL SELECT 'a',2 
 UNION ALL SELECT 'b',1 
 UNION ALL SELECT 'b',2 
 UNION ALL SELECT 'c',3 
  
 --合并处理 
 SELECT col1, 
     col2=CAST(MIN(col2) as varchar) 
         +CASE  
             WHEN COUNT(*)=1 THEN '' 
             ELSE ','+CAST(MAX(col2) as varchar) 
         END 
 FROM tb 
 GROUP BY col1 
 DROP TABLE tb 
 /*--结果 
 col1       col2       
 ---------- ---------- 
 a          1,2 
 b          1,2 
 c          3 
 --*/ 
  
 --3.3.4.2 每组  <=3 条记录的合并 
 --处理的数据 
 CREATE TABLE tb(col1 varchar(10),col2 int) 
 INSERT tb SELECT 'a',1 
 UNION ALL SELECT 'a',2 
 UNION ALL SELECT 'b',1 
 UNION ALL SELECT 'b',2 
 UNION ALL SELECT 'b',3 
 UNION ALL SELECT 'c',3 
  
 --合并处理 
 SELECT col1, 
     col2=CAST(MIN(col2) as varchar) 
         +CASE  
             WHEN COUNT(*)=3 THEN ',' 
                 +CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar) 
             ELSE '' 
         END 
         +CASE  
             WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar) 
             ELSE '' 
         END 
 FROM tb a 
 GROUP BY col1 
 DROP TABLE tb 
 /*--结果 
 col1       col2 
 ---------- ------------ 
 a          1,2 
 b          1,2,3 
 c          3 
 --*/ 
 GO 
 if not object_id('A') is null 
     drop table A 
 Go 
 Create table A([id] int,[cname] nvarchar(2)) 
 Insert A 
 select 1,N'张三' union all 
 select 2,N'李四' union all 
 select 3,N'王五' union all 
 select 4,N'蔡六' 
 Go 
 --> -->  
   
 if not object_id('B') is null 
     drop table B 
 Go 
 Create table B([id] int,[cname] nvarchar(5)) 
 Insert B 
 select 1,N'1,2,3' union all 
 select 2,N'3,4' 
 Go 
 create function F_str(@cname nvarchar(100)) 
 returns nvarchar(100) 
 as 
 begin  
 select @cname=replace(@cname,ID,[cname]) from A where patindex('%,'+rtrim(ID)+',%',','+@cname+',')>0 
 return @cname 
 end 
 go 
 select [id],dbo.F_str([cname])[cname] from B 
  
 id          cname 
 ----------- ---------------------------------------------------------------------------------------------------- 
 1           张三,李四,王五 
 2           王五,蔡六