Tuesday, June 19, 2012

Comma separate with quirky update

create table Juls (
    PersonID int,
    Degree varchar(3) )
insert juls
select 55,'md'
union select 55,'phd'
union select 55,'rn'
union select 60,'md'
union select 60,'phd'

declare @List varchar(100), @LastID int
select @List = '', @LastID = ''
select
    PersonID,
    Degree,
    convert(varchar(100),NULL) as list
into
    #rowset
from
    Juls
order by
    PersonID,
    Degree
update
    #rowset
set
    @List = list = case
                       when @LastID <> PersonID then Degree
                       else @List + ',' + Degree
                   end,
    @LastID = PersonID
select
    PersonID,
    max(list)
from
    #rowset
group by
    PersonID
go
drop table #rowset

No comments:

Post a Comment