Turn multiple column values into one row value

[Kaiaualad]

Hi, I have data in a table that looks thus:

Event_number Risk_Code

1 A
1 B
1 C
2 A
2 D
2 G

I need it to look like this:

Event_number Risk_code

1 A B C
2 A D G

Can this be done in SQL or is it an array type function?

 

[donutman]

thread183-832209
-Karl

 

[NathanGriffiths]

Its a bit long-winded for my liking but anyway;

create table #result (EventNum int, risk_codes varchar(20))
insert into #result (eventnum) select distinct eventnum from test

set concat_null_yields_null off

declare 
	@old_rc char(1), 
	@new_rc char(1), 
	@old_en int, 
	@new_en int, 
	@enmax int, 
	@rcmax char(1)

set @old_en = 0
set @new_en = 0

select @enmax = max(eventnum) from #result

while @old_en < @enmax
	begin
		select top 1 @new_en = eventnum from #result where eventnum > @old_en
		select @rcmax = max(risk_code) from test where eventnum = @new_en
		set @old_rc = ''
		set @new_rc = ''
		while @new_rc < @rcmax
			begin 
				select top 1 @new_rc = risk_code from test where eventnum = @new_en and risk_code > @old_rc
				update #result set risk_codes = risk_codes + @new_rc where eventnum = @new_en
				set @old_rc = @new_rc
		 	end
		set @old_en = @new_en
	end


SELECT * FROM #result

Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------