469,307 Members | 2,482 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,307 developers. It's quick & easy.

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

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
4 3623
ck9663
2,878 Expert 2GB
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
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
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
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.

Similar topics

8 posts views Thread by Rigga | last post: by
8 posts views Thread by Lauren Quantrell | last post: by
68 posts views Thread by Martin Joergensen | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.