请教怎么写这条sql语句

Question

合并列值
原著：邹建
改编：爱新觉罗.毓华  2007-12-16  广东深圳表结构，数据如下：
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. 旧的解决方法(在sql server 2000中只能用函数解决。)
--1. 创建处理函数
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
goCREATE 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, value = dbo.f_str(id) FROM tb GROUP BY iddrop table tb
drop function dbo.f_str/*
id          value      
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
（所影响的行数为 2 行）
*/--2、另外一种函数.
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go--创建一个合并的函数
create function f_hb(@id int)
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(value as varchar) from tb where id = @id
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：
select distinct id ,dbo.f_hb(id) as value from tbdrop table tb
drop function dbo.f_hb/*
id          value      
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
（所影响的行数为 2 行）
*/2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
-- 查询处理
SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(
        SELECT [values]= STUFF(REPLACE(REPLACE(
            (
                SELECT value FROM tb N
                WHERE id = A.id
                FOR XML AUTO
            ), '<N value="', ','), '"/>', ''), 1, 1, '')
)N
drop table tb/*
id          values
----------- -----------
1           aa,bb
2           aaa,bbb,ccc(2 行受影响)
*/

Answer 1

--sql server 2000中使用函数完成.
create table Table1(ID varchar(10) , carID varchar(10), Start varchar(10), [End] varchar(10) )
insert into table1 values('C001', '京A11111', '北京', '上海') 
insert into table1 values('C002', '沪A11111', '上海', '西安')
create table Table2(ID varchar(10), Pass varchar(10))
insert into table2 values('C001', '天津') 
insert into table2 values('C001', '石家庄') 
insert into table2 values('C001', '南京') 
insert into table2 values('C001', '苏州') 
insert into table2 values('C002', '苏州') 
insert into table2 values('C002', '郑州') 
insert into table2 values('C002', '太原') 
go--创建一个合并的函数
create function f_hb(@id varchar(10))
returns varchar(8000)
as
begin
 declare @str varchar(8000)
 set @str = ''
 select @str = @str + ',' + cast(pass as varchar) from table2 where id = @id
 set @str = right(@str , len(@str) - 1)
 return(@str)
End
go--调用自定义函数得到结果：
select m.ID,m.carID,m.Start,n.Pass,m.[End] from table1 m,
(select distinct id ,dbo.f_hb(id) as pass from table2) n
where m.id = n.iddrop function dbo.f_hb
drop table table1,table2/*
ID         carID      Start      Pass                 End 
---------- ---------- ---------- -------------------- -------
C001       京A11111   北京       天津,石家庄,南京,苏州  上海
C002       沪A11111   上海       苏州,郑州,太原         西安（所影响的行数为 2 行）
*/

Answer 2

--sql server 2005中用OUTER APPLY完成.
create table Table1(ID varchar(10) , carID varchar(10), Start varchar(10), [End] varchar(10) )
insert into table1 values('C001', '京A11111', '北京', '上海') 
insert into table1 values('C002', '沪A11111', '上海', '西安')
create table Table2(ID varchar(10), Pass varchar(10))
insert into table2 values('C001', '天津') 
insert into table2 values('C001', '石家庄') 
insert into table2 values('C001', '南京') 
insert into table2 values('C001', '苏州') 
insert into table2 values('C002', '苏州') 
insert into table2 values('C002', '郑州') 
insert into table2 values('C002', '太原') 
goselect t1.ID,t1.carID,t1.Start,t2.Pass,t1.[End] from table1 t1,
(SELECT * FROM(SELECT DISTINCT id FROM table2)A OUTER APPLY(
        SELECT [Pass]= STUFF(REPLACE(REPLACE(
            (
                SELECT Pass FROM table2 N
                WHERE id = A.id
                FOR XML AUTO
            ), '<N Pass="', ','), '"/>', ''), 1, 1, '')
)N) t2
where t1.id = t2.iddrop table table1,table2/*
ID         carID      Start      Pass                 End 
---------- ---------- ---------- -------------------- -------
C001       京A11111   北京       天津,石家庄,南京,苏州  上海
C002       沪A11111   上海       苏州,郑州,太原         西安(2 行受影响)
*/

Answer 3

CREATE TABLE Table1(ID varchar(10),carID varchar(20),Start varchar(20),[End] varchar(20))
insert into Table1 values('C001',   '京A11111',     '北京',       '上海') 
insert into Table1 values('C002',   '沪A11111',     '上海',       '西安') 
go
CREATE TABLE Table2(ID varchar(10),Pass varchar(10))
insert into Table2 values('C001',   '天津') 
insert into Table2 values('C001',   '石家庄') 
insert into Table2 values('C001',   '南京')
insert into Table2 values('C001',   '苏州') 
insert into Table2 values('C002',   '苏州') 
insert into Table2 values('C002',   '郑州') 
insert into Table2 values('C002',   '太原') 
go
create function f_form_string
(
@ID varchar(10)
)
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',' + Pass from table2 where ID = @ID
    select @sql = substring(@sql,2,8000)
return @sql
end
goselect ID,carID,Start,dbo.f_form_string(ID)as Pass,[End] from Table1--drop table table1
--drop table table2
--drop function f_form_string

Answer 4

---定义一个函数
CREATE FUNCTION dbo.StrLink(@id varchar(50))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @ColStr VARCHAR(8000)
SET @ColStr=''
SELECT @ColStr=@ColStr+','+FieldName FROM table2 WHERE [id]=@id
IF LEN(@ColStr)>0
BEGIN
SET @ColStr=RIGHT(@ColStr,LEN(@ColStr)-1)
END
RETURN @ColStr
END
GOselect t1.ID,t1.carID,t1.Start,t2.Pass,t1.[End] from table1 t1 inner join
(select id ,dbo.StrLink(id) as pass from table2  group by id )t2ON t1.id=t2.id