日期:2014-05-18 浏览次数:20563 次
CREATE TABLE [dbo].[AB]( [AA] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [BB] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]
insert into AB(AA,AB)values('A1,A2,A3,A4,A5,A6,A7','A5,A3,A7') insert into AB(AA,AB)values('张三,李四,王五,赵柳,王八','王八,李四,张三')
AA A1,A2,A3(有),A4,A5(有),A6,A7(有) 张三(有),李四(有),王五,赵柳,王八(有)
/* CREATE FUNCTION [dbo].[m_BB] ( @a VARCHAR(2000) , @b VARCHAR(2000) , @split VARCHAR(2) ) RETURNS VARCHAR(2000) AS BEGIN DECLARE @t1 TABLE ( col VARCHAR(2000) ) WHILE ( CHARINDEX(@split, @a) <> 0 ) BEGIN IF ( SUBSTRING(@a, 1, CHARINDEX(@split, @a) - 1) != ' ' ) BEGIN INSERT @t1 ( col ) VALUES ( SUBSTRING(@a, 1, CHARINDEX(@split, @a) - 1) ) END SET @a = STUFF(@a, 1, CHARINDEX(@split, @a), '') END INSERT @t1 ( col ) VALUES ( @a ) DECLARE @t2 TABLE ( col VARCHAR(2000) ) WHILE ( CHARINDEX(@split, @b) <> 0 ) BEGIN IF ( SUBSTRING(@b, 1, CHARINDEX(@split, @b) - 1) != ' ' ) BEGIN INSERT @t2 ( col ) VALUES ( SUBSTRING(@b, 1, CHARINDEX(@split, @b) - 1) ) END SET @b = STUFF(@b, 1, CHARINDEX(@split, @b), '') END INSERT @t2 ( col ) VALUES ( @b ) DECLARE @sql VARCHAR(2000) SET @sql = '' SELECT @sql = @sql + ISNULL(b.col + '(有)', a.col) + ',' FROM @t1 a LEFT JOIN @t2 b ON a.col = b.col RETURN @sql END */ SELECT [dbo].[m_BB](AA,BB,',') AS AA FROM AB /* AA ------------------------------------------- A1,A2,A3(有),A4,A5(有),A6,A7(有), 张三(有),李四(有),王五,赵柳,王八(有), */
------解决方案--------------------
----drop table ab CREATE TABLE [dbo].[AB]( [AA] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [BB] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [CC] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] insert into AB(AA,BB)values('A1,A2,A3,A4,A5,A6,A7','A5,A3,A7') insert into AB(AA,BB)values('张三,李四,王五,赵柳,王八','王八,李四,张三') update AB set CC = AA /**** 将以上数据修改为下面的样子 A1,A2,A3(有),A4,A5(有),A6,A7(有) 张三(有),李四(有),王五,赵柳,王八(有) ***/ declare @string table (c_string varchar(20)) delete @string declare @bb varchar(50),@bb1 varchar(50) declare @i int,@i0 int ,@tmp varchar(100) ---第一次游标,将bb字段的逗号分隔符字符串变成表变量 declare curs_ab1 cursor for select bb from ab open curs_ab1 fetch curs_ab1 into @bb while @@fetch_status=0 begin delete @string select @bb=ltrim(rtrim(isnull(@bb,''))) set @i=1 set @i0 = 0 while(@i<=len(@bb)) begin if (substring(@bb,@i,1)=',') or