hats off to you...

Thanks for the query..

I have derived the output but that was quite complex

create table SampleTable2

(id int identity(1,1) ,Categoryvarchar(100), Organisams varchar(100) )

insert into SampleTable2(Category)

select distinct Category from sampletable

DECLARE @listStr VARCHAR(MAX) ,@Category varchar(100),@i int

set @i =1

while (@i <= (select MAX(id)from SampleTable2 ))

begin

set @Category = (select category from SampleTable2 where id = @i)

SELECT @listStr =''

SELECT @listStr = COALESCE(@listStr,'') +','+Organisams

FROM SampleTable where category=@category

update SampleTable2 set Organisams = right(@listStr,LEN(@listStr)-1)

where Category = @Category

set @i = @i+1

End