加分隔符的行列转换问题？

Question

举例：select id,num from table 的结果为
1  2
2  4
3  6
等
要求转换为 
1,2,3和 2,4,6
其中（,）为分割符号，最好写成函数。

Answer 1

----创建测试数据
declare @t table(id int,num int)
insert @t
select 1,2 union all
select 2,4 union all
select 3,6----查询
declare @idList varchar(1000),@numList varchar(1000)
set @idList = ''
set @numList = ''
select 
@idList = case @idList when '' then '' else @idList + ',' end + rtrim(id),
@numList = case @numList when '' then '' else @numList + ',' end + rtrim(num)
from @tselect @idList as id,@numList as num/*结果
id      num
------------------
1,2,3   2,4,6
*/

Answer 2

有表tb, 如下：
id    value
----- ------
1     aa
1     bb
2     aaa
2     bbb
2     ccc需要得到结果：
id     values
------ -----------
1      aa,bb
2      aaa,bbb,ccc在SQL Server2000中我们处理的方式一般都是用自定义函数去处理：
举例如下：create 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在SQL Server2005中，这种统计有了一种新的方法，直接使用语句调用sql2005的函数实现：
举例如下：
select id, values=dbo.f_str(id) from tb group by id-- 示例数据
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
)A
OUTER 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,bb
2           aaa,bbb,ccc(2 行受影响)
--*/
最近从CSDN上又发现了一种方法！
declare @a varchar(100)
select @a=coalesce(@a+'/','')+PNAME from HPINF where perid like'207%'
select @a

Answer 3

create  table t(id int,num int)
insert t
select 1,2 union all
select 2,4 union all
select 3,6
go---跟常见的没什么大区别,只是select时没有where而已
create function f1()
returns varchar(100) 
as
begin
declare @s varchar(100)
set @s=''
select @s=@s+','+cast(id as varchar) from t 
return stuff(@s,1,1,'')
end
go
create function f2()
returns varchar(100) 
as
begin
declare @s varchar(100)
set @s=''
select @s=@s+','+cast(Num as varchar) from t 
return stuff(@s,1,1,'')
end
go
select top 1 dbo.f1(),dbo.f2() from t 
drop table t
drop function f1
drop function f2

Answer 4

Create Function stest
(
@A Int,
@B int
)
Returns @temp table(
C1 varchar(1000),
C2 varchar(1000)
)
As
Begin
Declare @C Varchar(2000)
Declare @D Varchar(2000)
Select @C = ''
Select @D = ''
Select @C = @C + '' + [id]+',' 
From [table]
Select @C=left(@C,len(@C)-1)
Select @D = @D + '' + [num]+',' 
From [table]
Select @D=left(@D,len(@D)-1)
insert into @temp select @C,@D
Return 
End

Answer 5

Create Function stest
(
@A Int,
@B int
)
Returns @temp table(
C1 varchar(1000),
C2 varchar(1000)
)
As
Begin
Declare @C Varchar(2000)
Declare @D Varchar(2000)
Select @C = ''
Select @D = ''
Select @C = @C + '' + [id]+',' 
From [table]
Select @C=left(@C,len(@C)-1)
Select @D = @D + '' + [num]+',' 
From [table]
Select @D=left(@D,len(@D)-1)
insert into @temp select @C,@D
Return 
End