September 18, 2003 at 2:15 am
I need to pivot multiple rows into one column of a result set.
create table test (col1 int, col2 char(3))
insert test values (1, 'aaa')
insert test values (2, 'bbb')
insert test values (2, 'ccc')
insert test values (2, 'ddd')
insert test values (3, 'eee')
insert test values (4, 'ccc')
insert test values (4, 'eee')
insert test values (4, 'aaa')
I want to select all unique col1 values for a given col2 but also get back a column of comma separated col2 values that also have the same col1
For example, for col2='ccc' I want this result:
col1 col2 col3
=== ==== ===========
2 "ccc" "bbb,ccc,ddd"
4 "ccc" "eee,ccc,aaa"
even better would be to eliminate 'ccc' from Col3 above....
I can do it with a cursor and temp tables, but anyone got a non-cursor solution?
Thanks
September 18, 2003 at 2:38 am
select col1,hotel.dbo.ftest(col1)
from test
group by col1
alter function ftest(@col1 int)
returns varchar(1000)
as
begin
declare @m varchar(1000)
set @m=''
select @m=@m+','+col2 from test where col1=@col1
return substring(@m,2,len(@m))
end
He who knows others is learned but the wise one is one who knows himself.
He who knows others is learned but the wise one is one who knows himself.
September 18, 2003 at 2:42 am
Cool, we're just in the process of moving to SQL2K and I haven't made the move in my head yet to think of using functions. 'bout time I started I see.
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply