MS SQL Server Multiple Rows into 1 column.. Semicolon Delimited.. Null Value Problem

[JovieUrbano]

I have a question. I am working on something based on the information I got from this forum.

I am using the sample I found and modifying it a little to get the results I want..

--TABLE--
Create table test
(PersonID varchar(2),
Degree varchar(10))

insert into test values ('55', 'MD')
insert into test values ('55', 'PhD')
insert into test values ('55', 'RN')
insert into test values ('60', 'MD')
insert into test values ('60', 'PhD')

--SO FAR THIS IS WHAT I HAVE

declare @DegName varchar(50)
declare @Sql nvarchar(4000)

declare c cursor FAST_FORWARD for
select distinct degree from test order by degree

open c
fetch next from c into @DegName

set @Sql = 'select personid, '
while @@Fetch_Status = 0
begin
set @Sql = @Sql + '+ Min(Case when Degree = ''' + @DegName + ''' then
degree end) + ''; '' '
fetch next from c into @DegName
end
close c
deallocate c
set @Sql = @Sql + 'as Degree'
print @sql
set @Sql = @Sql + ' from test group by PersonId '
print @sql
exec (@sql)

--DESIRED RESULTS--
PersonID Degree
55 MD; PhD; RN
60 MD; PhD

--ACTUAL RESULTS--
PersonID Degree
55 MD; PhD; RN
60 NULL <--- I need help in changing this to MD; PhD

I am only using SQL Server 2000.. I am open to other methods as long as the Desired result is achieved

[ck9663]

I did not read the rest of your code after this:

select distinct degree from test order by degree

Don't you think you should have a list of distinct PersonId and not degree? From the look of your desired result, you want all rows from your test table into 1 row if they all have the same PersonId. You have a list of distinct degree, which could have multiple PersonId.Should it be the other way around?

-- CK

[JovieUrbano]

I did not read the rest of your code after this:

select distinct degree from test order by degree

Don't you think you should have a list of distinct PersonId and not degree? From the look of your desired result, you want all rows from your test table into 1 row if they all have the same PersonId. You have a list of distinct degree, which could have multiple PersonId.Should it be the other way around?

-- CK

ok. do you have an idea on how you are going to do it?

I already made a research, the desired result is not obtainable using a simple Select Statement..

[JovieUrbano]

please help.. I got this Dynamic SQL here but I tweaked it a little bit. Also, there is a group by personID in the statement.

[kbramkumar]

Try this method

DECLARE @EmployeeList varchar(8000)

SELECT @EmployeeList = COALESCE(@Emplo yeeList + ', ', '') + FirstName

FROM (
select 'Jose' as FirstName
union
select 'Haprise' as FirstName
)aa

SELECT @EmployeeList

BR
Ramkumar