请教这个问题怎样做？

Question

表：id1   id2
     1     a
     2     b
     3     c
     ...   ...
我想将其转换成两个字符id1（1,2,3,...）,id2(a,b,c,...)
请高手指教？  

Answer 1

select stuff((select ','+ltrim(id1) from tb for xml path('')),1,1,'') as id1,
    stuff((select ','+ltrim(id2) from tb for xml path('')),1,1,'') as id2

Answer 2

CREATE FUNCTION dbo.f_str1()
RETURNS varchar(8000)
AS
BEGIN
  DECLARE @r varchar(8000)
  SET @r = ''
  SELECT @r = @r + ',' + id1 FROM tb 
  RETURN STUFF(@r, 1, 1, '')
END
GO
CREATE FUNCTION dbo.f_str2()
RETURNS varchar(8000)
AS
BEGIN
  DECLARE @r varchar(8000)
  SET @r = ''
  SELECT @r = @r + ',' + id2 FROM tb 
  RETURN STUFF(@r, 1, 1, '')
END
GO
select dbo.f_str1,dbo.f_str2

Answer 3

create table tb(id1 int,id2 int)
insert into tb
select 1,2 union all
select 2,3 union all
select 3,9
gocreate function get_all()
returns @t table
(
id1 varchar(100),
id2 varchar(100)
)
as
begin
declare @ret1 varchar(100)
declare @ret2 varchar(100)select @ret1 = isnull(@ret1,'') + ',' + ltrim(id1) from tb
select @ret2 = isnull(@ret2,'') + ',' + ltrim(id2) from tbinsert into @t
select stuff(@ret1,1,1,''),stuff(@ret2,1,1,'')
return
end
goselect * from dbo.get_all()drop function get_all
drop table tb
/*****************************id1                                                                                                  id2
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1,2,3                                                                                                2,3,9(1 行受影响)

Answer 4

create table tb(id1 int,id2 varchar(10))
insert into tb select 1,'a'
insert into tb select 2,'b'
insert into tb select 3,'c'
go
declare @a varchar(100),@b varchar(100)
select @a=isnull(@a+',','')+ltrim(id1),@b=isnull(@b+',','')+id2 from tb
select @a id1,@b id2
/*
id1                                                                                                  id2
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1,2,3                                                                                                a,b,c(1 行受影响)
*/
go
drop table tb