By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,935 Members | 1,380 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,935 IT Pros & Developers. It's quick & easy.

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

P: 8
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
Feb 20 '08 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
I did not read the rest of your code after this:

Expand|Select|Wrap|Line Numbers
  1. 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
Feb 20 '08 #2

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

Expand|Select|Wrap|Line Numbers
  1. 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..
Feb 20 '08 #3

P: 8
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.
Feb 22 '08 #4

P: 1
Try this method

DECLARE @EmployeeList varchar(8000)

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

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

SELECT @EmployeeList

BR
Ramkumar
Feb 29 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.