Combine Rows as a single column

[dhulbert]

I have data in a table as shown below. I want ot combine all of the activity codes into a single column possible comma seperated

So

Session Activity
1 a1
1 a2
1 b1
2 a2
2 b1

Becomes

Session Activity
1 a1, a2, b1
2 a2, b1

I had a look at Pivot but that doesn;t seem to work for this.

Any ideas happily recieved.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[SimonSellick]

You can use a function to concatenate rows together:

create table tbl(session int, activity varchar(2))
insert into tbl values (1, 'a1')
insert into tbl values (1, 'a2')
insert into tbl values (1, 'b1')
insert into tbl values (2, 'a2')
insert into tbl values (2, 'b1')
go
create function dbo.ConcatRows( 
  @session int
) returns varchar(20) as
begin
  declare @r varchar(20) 
  select @r = isnull(@r + ', ', '') + activity
  from tbl
  where session = @session
  return @r 
end
go
select * from tbl
select distinct session, dbo.ConcatRows(session) from tbl
drop function dbo.ConcatRows
drop table tbl

 

[dhulbert]

Not that look like what I'm trying to do.

I'll give it a go.

Thanks.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[simian336]

See....

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Try the FOR XML approach.

Simi