create table test22
( id varchar(5)
, temp varchar(10)
, state varchar(1)
)
insert into test22
select 'aaaaa',                      '사과'            ,                 '1' union all
select 'ccccc'      ,                '바나나'                   ,       '2' union all
select 'aaaaa'       ,               '오렌지'                   ,       '3' union all
select 'bbbbb'        ,              '배'                         ,       '1' union all
select 'bbbbb'         ,              '사과'                     ,       '5'

CREATE FUNCTION fn_getTemp (@id varchar(5))
RETURNS VARCHAR(1000)
AS
BEGIN
 DECLARE @temp VARCHAR(1000)
 SELECT @temp = COALESCE(@temp +',' +  temp, temp)  FROM test22
 WHERE id = @id
 RETURN @temp
END

CREATE FUNCTION fn_getState (@id varchar(5))
RETURNS VARCHAR(1000)
AS
BEGIN
 DECLARE @temp VARCHAR(1000)
 SELECT @temp = COALESCE(@temp +',' +  state, state)  FROM test22
 WHERE id = @id
 RETURN @temp
END

select id, dbo.fn_getTemp(id), dbo.fn_getState(id) from test22
group by id