列转换成用逗号隔开的行解决方案

列转换成用逗号隔开的行
例如
id type 
1 a
2 b
3 a
4 a
5 a
6 b
7 c 
8 a
变成 增加一列的新表
id type type_hz
1 a 1,3,4,5,8
2 b 2,6
3 a 1,3,4,5,8
4 a 1,3,4,5,8
5 a 1,3,4,5,8
6 b 2,6
7 c 7
8 a 1,3,4,5,8

我会用游标写,但是慢所以不要用游标,不要用自定义函数,移植不好

求教






[解决办法]

SQL code

create table tb( id int, type varchar(1))insert into tbselect 1,'a' union allselect 2,'b' union allselect 3, 'a' union allselect 4, 'a' union allselect 5, 'a' union allselect 6, 'b' union allselect 7, 'c' union allselect 8, 'a'--个人感觉用函数最简单，切移植性更好(只要修改函数即可)create function F_GetType_hz(@typeid varchar(1)) returns varchar(1000) AS begin    declare @s varchar(1000)    select @s=isnull(@s+',' , '')+ cast(id as varchar) from tb where type =@typeid    return @sendselect *,type_hz=dbo.F_GetType_hz(type) from tb
[解决办法]
SQL code
 if not object_id('Tempdb..#T') is null    drop table #TGoCreate table #T([id] int,[type] nvarchar(1))Insert #Tselect 1,N'a' union allselect 2,N'b' union allselect 3,N'a' union allselect 4,N'a' union allselect 5,N'a' union allselect 6,N'b' union allselect 7,N'c' union allselect 8,N'a'GoSelect *,       stuff((select ','+cast([ID] as varchar(10))              from #t              where [type]=t.[type]              for xml path('')),1,1,'') from #T t
[解决办法]
SQL code
create table tb( id int, type varchar(1))insert into tbselect 1,'a' union allselect 2,'b' union allselect 3, 'a' union allselect 4, 'a' union allselect 5, 'a' union allselect 6, 'b' union allselect 7, 'c' union allselect 8, 'a'SELECT *FROM(    SELECT          id,type    FROM tb)AOUTER APPLY(    SELECT         type_hz= STUFF(REPLACE(REPLACE(            (                SELECT id FROM tb B                WHERE type = A.type                 FOR XML AUTO            ), '<B id="', ','), '"/>', ''), 1, 1, ''))B
[解决办法]
for sql2000的方法.
SQL code
create table jic(id int, typei char(1))insert into jicselect 1, 'a' union allselect 2, 'b' union allselect 3, 'a' union allselect 4, 'a' union allselect 5, 'a' union allselect 6, 'b' union allselect 7, 'c' union allselect 8, 'a'-- create functioncreate function fn_typehz(@typei char(1))returns varchar(50)asbegin  declare @r varchar(50)=''  select @r=@r+','+cast(id as varchar)     from jic where typei=@typei    return stuff(@r,1,1,'') end-- use functionselect id,typei,dbo.fn_typehz(typei) 'type_hz'from jic-- resultid          typei type_hz----------- ----- -------------1           a     1,3,4,5,82           b     2,63           a     1,3,4,5,84           a     1,3,4,5,85           a     1,3,4,5,86           b     2,67           c     78           a     1,3,4,5,8(8 row(s) affected)