sql 2000 合并行的有关问题

SELECT  JDBH ,                        JSBM = STUFF(( SELECT   ',' + jdbm                                       FROM     View_SzGcxm_old t                                       WHERE    jdbh = z.jdbh                                     FOR                                       XML PATH('')                                     ), 1, 1, '') ,                        XXJDY = STUFF(( SELECT  ',' + jdxz                                        FROM    View_SzGcxm_old t                                        WHERE   jdbh = z.jdbh                                      FOR                                        XML PATH('')                                      ), 1, 1, '') ,                        jdbmName = STUFF(( SELECT   ',' + jdbmName                                           FROM     View_SzGcxm_old t                                           WHERE    jdbh = z.jdbh                                         FOR                                           XML PATH('')                                         ), 1, 1, '')                FROM    View_SzGcxm_old z                GROUP BY jdbh

if not object_id('B') is null    drop table BGoCreate table B([id] int,[cname] nvarchar(5))Insert Bselect 1,N'1,2,3' union allselect 2,N'3,4'Gocreate function F_str(@cname nvarchar(100))returns nvarchar(100)asbegin select @cname=replace(@cname,ID,[cname]) from A where patindex('%,'+rtrim(ID)+',%',',[email protected]+',')>0return @cnameendgoselect [id],dbo.F_str([cname])[cname] from Bid          cname1           张三,李四,王五2           王五,蔡六
------解决方案--------------------
SQL code
表a name    num aa      1 bb      2 cc      4 dd      4 ee      2 一条语句实现如下:name aa,bb,cc,dd,ee DECLARE @STR VARCHAR(8000)SELECT @STR=ISNULL(@STR+',','')+name FROM (SELECT DISTINCT NAME FROM A)AS TSELECT @STR问题描述：无论是在sql 2000，还是在sql 2005 中,都没有提供字符串的聚合函数，  所以，当我们在处理下列要求时，会比较麻烦：有表tb, 如下：id    value1     aa1     bb2     aaa2     bbb2     ccc需要得到结果：id     values1      aa,bb2      aaa,bbb,ccc即，group by id, 求value 的和（字符串相加）1. 旧的解决方法-- 1. 创建处理函数CREATE FUNCTION dbo.f_str(@id int)RETURNS varchar(8000)ASBEGIN    DECLARE @r varchar(8000)    SET @r = ''    SELECT @r = @r + ',' + value    FROM tb    WHERE [email protected]    RETURN STUFF(@r, 1, 1, '')ENDGO-- 调用函数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)AOUTER 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,bb2           aaa,bbb,ccc(2 行受影响)--*/--各种字符串分函数--3.3.1 使用游标法进行字符串合并处理的示例。--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3--合并处理--定义结果集表变量DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))--定义游标并进行合并处理DECLARE tb CURSOR LOCALFORSELECT col1,col2 FROM tb ORDER BY  col1,col2DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)OPEN tbFETCH tb INTO @col1,@col2SELECT @[email protected],@s=''WHILE @@FETCH_STATUS=0BEGIN    IF @[email protected]_old        SELECT @[email protected]+','+CAST(@col2 as varchar)    ELSE    BEGIN        INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))        SELECT @s=','+CAST(@col2 as varchar),@[email protected]    END    FETCH tb INTO @col1,@col2ENDINSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))CLOSE tbDEALLOCATE tb--显示结果并删除测试数据SELECT * FROM @tDROP TABLE tb/*--结果col1       col2a          1,2b          1,2,3--*/GO--3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3GO--合并处理函数CREATE FUNCTION dbo.f_str(@col1 varchar(10))RETURNS varchar(100)ASBEGIN    DECLARE @re varchar(100)    SET @re=''    SELECT @[email protected]+','+CAST(col2 as varchar)    FROM tb    WHERE [email protected]    RETURN(STUFF(@re,1,1,''))ENDGO--调用函数SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1--删除测试DROP TABLE tbDROP FUNCTION f_str/*--结果col1       col2a          1,2b          1,2,3--*/GO--3.3.3 使用临时表实现字符串合并处理的示例--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3--合并处理SELECT col1,col2=CAST(col2 as varchar(100)) INTO #t FROM tbORDER BY col1,col2DECLARE @col1 varchar(10),@col2 varchar(100)UPDATE #t SET     @col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END,    @col1=col1,    [email protected]SELECT * FROM #t/*--更新处理后的临时表col1       col2a          1a          1,2b          1b          1,2b          1,2,3--*/--得到最终结果SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1/*--结果col1       col2a          1,2b          1,2,3--*/--删除测试DROP TABLE tb,#tGO--3.3.4.1 每组<=2 条记录的合并--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'c',3--合并处理SELECT col1,    col2=CAST(MIN(col2) as varchar)        +CASE             WHEN COUNT(*)=1 THEN ''            ELSE ','+CAST(MAX(col2) as varchar)        ENDFROM tbGROUP BY col1DROP TABLE tb/*--结果col1       col2      ---------- ----------a          1,2b          1,2c          3--*/--3.3.4.2 每组<=3 条记录的合并--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3UNION 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 ''        ENDFROM tb aGROUP BY col1DROP TABLE tb/*--结果col1       col2---------- ------------a          1,2b          1,2,3c          3--*/GOif not object_id('A') is null    drop table AGoCreate table A([id] int,[cname] nvarchar(2))Insert Aselect 1,N'张三' union allselect 2,N'李四' union allselect 3,N'王五' union allselect 4,N'蔡六'Go--> -->  if not object_id('B') is null    drop table BGoCreate table B([id] int,[cname] nvarchar(5))Insert Bselect 1,N'1,2,3' union allselect 2,N'3,4'Gocreate function F_str(@cname nvarchar(100))returns nvarchar(100)asbegin select @cname=replace(@cname,ID,[cname]) from A where patindex('%,'+rtrim(ID)+',%',',[email protected]+',')>0return @cnameendgoselect [id],dbo.F_str([cname])[cname] from Bid          cname1           张三,李四,王五2           王五,蔡六(2 個資料列受到影響)我现在有一字段值为:a,b,c 分隔符为逗号. 我现在想实现目地值为: 字段: id  name 1  a 2  b 3  c 在SQL中咋实现呢?declare @str varchar(8000) set @str = 'a1,b1,c2,d1,e3,f5' set @str =  'select  name='''+replace(@str,',',''''+' union all select ''')+'''' set @str='select id=identity(int,1,1),name into #temp from ([email protected]+') a select * from #temp drop table #temp'exec(@str)--参考:CREATE TABLE TB(ID VARCHAR(6), COLOR NVARCHAR(30))INSERT TBSELECT '173160',  N'#特深蓝色,#特深蓝色' UNION ALL SELECT '173160',  N'#特深蓝色,#特深蓝色' UNION ALL SELECT '911169',  N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL SELECT '911169',  N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL SELECT '911169',  N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL SELECT '911169',  N'#宝蓝色,#宝蓝色,#花灰色,#花灰色'GOCREATE FUNCTION F_getStr(@color nvarchar(30))returns nvarchar(30)asbegin  declare @str nvarchar(30),@temp nvarchar(30)  set @str=''  set @temp=''  while charindex(',', @color+',')>0  begin    set @temp=left(@color, charindex(',', @color+',')-1)    if charindex(',[email protected]+',', ',[email protected]+',')=0      set @[email protected]+',[email protected]    set @color=stuff(@color, 1, charindex(',', @color+','), '')      end  return stuff(@str, 1, 1, '')endgoSELECT ID,dbo.F_getStr(COLOR) as COLOR FROM TBDROP TABLE TBDROP FUNCTION F_getStr/*ID     COLOR173160 #特深蓝色173160 #特深蓝色911169 #宝蓝色,#花灰色911169 #宝蓝色,#花灰色911169 #宝蓝色,#花灰色911169 #宝蓝色,#花灰色*/