求一SQL语句或存储过程，关于修改字段内容的

Question

我有一个表，结构如下
表名A
字段：name，old，different
内容：
张山，20，null
张山，30，null
张山，35，null
李四，22，null
李四，20，null
我想得到下面结果
字段：name，old，different
内容：
张山，20，30、35
张山，30，20、35
张山，35，20、30
李四，22，20
李四，20，22就是将该name存在不同的old，得到后放入其different字段
建表语句
create table A
（
name varchar（20），
old varchar(20),
different varchar(200)）insert into A (name,old,different) values ('张山','20',null)
insert into A (name,old,different) values ('张山','30',null)
insert into A (name,old,different) values ('张山','35',null)
insert into A (name,old,different) values ('李四','20',null)
insert into A (name,old,different) values ('李四','22',null)求一SQL语句或存储过程,先谢谢各位老大

Answer 1

create table A
(
name varchar(20),
old varchar(20),
different varchar(200)
)insert into A (name,old,different) values ('张山','20',null)
insert into A (name,old,different) values ('张山','30',null)
insert into A (name,old,different) values ('张山','35',null)
insert into A (name,old,different) values ('李四','20',null)
insert into A (name,old,different) values ('李四','22',null)select t.name,t.old,
different=stuff((select '、'+ltrim(old) from a where name=t.name and old!=t.old for xml path('')),1,1,'')
from a t
/**
name                 old                  different
-------------------- -------------------- ----------------------------
张山                   20                   30、35
张山                   30                   20、35
张山                   35                   20、30
李四                   20                   22
李四                   22                   20(5 行受影响)
**/drop table a

Answer 2

create table A
(
name varchar(20),
old varchar(20),
different varchar(200))insert into A (name,old,different) values ('张山','20',null)
insert into A (name,old,different) values ('张山','30',null)
insert into A (name,old,different) values ('张山','35',null)
insert into A (name,old,different) values ('李四','20',null)
insert into A (name,old,different) values ('李四','22',null)CREATE FUNCTION getD(@name varchar(20),@old varchar(20))
RETURNS varchar(100)
AS 
BEGIN
DECLARE @sql varchar(100)
SELECT @sql=isnull(@sql+'、','')+old from a where NAME=@name and old<>@old 
RETURN @sql
END
SELECT NAME,old,dbo.getD(NAME,old) diffenent FROM a
--result
/*NAME                 old                  diffenent                      
-------------------- -------------------- ------------------------------ 
张山                   20                   30、35
张山                   30                   20、35
张山                   35                   20、30
李四                   20                   22
李四                   22                   20（所影响的行数为 5 行）*/

Answer 3

drop table A;
create table A
(
name varchar(20),
old varchar(20),
different varchar(200));insert into A (name,old,different) values ('张山','20',null)
insert into A (name,old,different) values ('张山','30',null)
insert into A (name,old,different) values ('张山','35',null)
insert into A (name,old,different) values ('李四','20',null)
insert into A (name,old,different) values ('李四','22',null)--创建函数
create function f_StrToLine(
    @name  nvarchar(20)
    ,@old  varchar(20)
)
returns varchar(4000)
as
begin
    declare @str varchar(4000)
    select @str = isnull(@str+'、','') + old
    from A
    where name = @name and old <> @old    return @str
end--查询结果
select * ,different = dbo.f_StrToLine(name,old)
from A--结果
/*
name                 old                  different                                                                                                                                                                                                different
-------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
张山                   20                   NULL                                                                                                                                                                                                     30、35
张山                   30                   NULL                                                                                                                                                                                                     20、35
张山                   35                   NULL                                                                                                                                                                                                     20、30
李四                   20                   NULL                                                                                                                                                                                                     22
李四                   22                   NULL                                                                                                                                                                                                     20(5 行受影响)
*/

Answer 4

update t
set t.different=stuff((select '、'+ltrim(old) from a where name=t.name and old!=t.old for xml path('')),1,1,'')
from a tselect * from a
/**
name                 old                  different
-------------------- -------------------- ----------------------------
张山                   20                   30、35
张山                   30                   20、35
张山                   35                   20、30
李四                   20                   22
李四                   22                   20(5 行受影响)
**/

Answer 5

create function get_old
(
   @name nvarchar(20),
   @old varchar(20)
)
returns nvarchar(20)
as
begin
declare @dif nvarchar(20)
select @dif=isnull(@dif,'')+old+','
from A
where [name]=@name and old!=@old
return @dif
endselect [name],old,left(dbo.get_old([name],old),len(dbo.get_old([name],old))-1) as different from A
/*
name                 old                  different
-------------------- -------------------- --------------------
张山                   20                   30,35
张山                   30                   20,35
张山                   35                   20,30
李四                   20                   22
李四                   22                   20(5 row(s) affected)
*/

Answer 6

create table Test
(
name varchar(20),
old varchar(20),
different varchar(200))insert into Test (name,old,different) values ('张山','20',null)
insert into Test (name,old,different) values ('张山','30',null)
insert into Test (name,old,different) values ('张山','35',null)
insert into Test (name,old,different) values ('李四','20',null)
insert into Test (name,old,different) values ('李四','22',null)create function GetStr(@name varchar(20),@old varchar(20))
RETURNS varchar(100)
AS 
BEGIN
    DECLARE @sql varchar(100)
    SELECT @sql=isnull(@sql+'、','')+old from Test where NAME=@name and old<>@old 
    RETURN @sql
ENDSELECT NAME,old,dbo.GetStr(name,old) diffenent FROM TestNAME                 old                  diffenent
-------------------- -------------------- ----------------------------------------------------------------------------------------------------
张山                   20                   30、35
张山                   30                   20、35
张山                   35                   20、30
李四                   20                   22
李四                   22                   20(5 行受影响)

Answer 7

非常感谢以上老大的解答，我想让语句更健壮和友好一点
1、得到的字符串不大于200字符，因为定义了different varchar(200)，大于可以忽略后面的字符
2、如果里面有2条以上“张山，30，NULL"，如何过滤得到的different字符串，如第一行的结果是张山，20，30、35。而不是张山，20，30、30、35        等一下就会结贴，再次感谢。                                                                                                                                              

Answer 8

create table A
(
name varchar(20),
old varchar(20),
different varchar(200))insert into A (name,old,different) values ('张山','20',null)
insert into A (name,old,different) values ('张山','30',null)
insert into A (name,old,different) values ('张山','30',null)
insert into A (name,old,different) values ('张山','35',null)
insert into A (name,old,different) values ('李四','20',null)
insert into A (name,old,different) values ('李四','22',null)
go
CREATE  FUNCTION getD(@name varchar(20),@old varchar(20))
RETURNS varchar(100)
AS 
BEGIN
    DECLARE @sql varchar(100)
    SELECT @sql=isnull(@sql+'、','')+old from (select distinct name,old,different from a) a where NAME=@name and old<>@old 
    RETURN @sql
END
SELECT NAME,old,dbo.getD(NAME,old) diffenent FROM a

Answer 9

NAME                 old                  diffenent                      
-------------------- -------------------- ------------------------------ 
张山                   20                   30、35
张山                   30                   20、35
张山                   30                   20、35
张山                   35                   20、30
李四                   20                   22
李四                   22                   20（所影响的行数为 6 行）