create table tb
(
 id int,
 type varchar(1)
)

insert into tb
select 1,'a' union all
select 2,'b' union all
select 3, 'a' union all
select 4, 'a' union all
select 5, 'a' union all
select 6, 'b' union all
select 7, 'c' union all
select 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 @s
end

select *,type_hz=dbo.F_GetType_hz(type) from tb

------解决方案--------------------

SQL code
 
if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([id] int,[type] nvarchar(1))
Insert #T
select 1,N'a' union all
select 2,N'b' union all
select 3,N'a' union all
select 4,N'a' union all
select 5,N'a' union all
select 6,N'b' union all
select 7,N'c' union all
select 8,N'a'
Go
Select *,
       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 tb
select 1,'a' union all
select 2,'b' union all
select 3, 'a' union all
select 4, 'a' union all
select 5, 'a' union all
select 6, 'b' union all
select 7, 'c' union all
select 8, 'a'


SELECT *
FROM(
    SELECT  
        id,type
    FROM tb
)A
OUTER 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 jic
select 1, 'a' union all
select 2, 'b' union all
select 3, 'a' union all
select 4, 'a' union all
select 5, 'a' union all
select 6, 'b' union all
select 7, 'c' union all
select 8, 'a'


-- create function
create function fn_typehz
(@typei char(1))
returns varchar(50)
as
begin
  declare @r varchar(50)=''
  select @r=@r+','+cast(id as varchar) 
    from jic where typei=@typei  
  return stuff(@r,1,1,'') 
end

-- use function
select id,typei,
dbo.fn_typehz(typei) 'type_hz'
from jic

-- result
id          typei 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

(8 row(s) affected)