Posted by gjvdkamp on 12/03/05 21:09

Here's another one:

If you know in advance what the different types of degrees are going to
be you can use this query:

select personid,
Min(Case when Degree = 'Md' then degree end) as 'Md',
Min(Case when Degree = 'Phd' then degree end) as 'Phd',
Min(Case when Degree = 'Rn' then degree end) as 'Rn'
from Degrees
group by PersonId

If you dont know in advance what degrees you can expect in the db, you
can use a cursor to produce the 'min(case ... end) as .., ' parts on
the fly:

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

declare c cursor FAST_FORWARD for
select distinct degree from degrees 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) as ''' + @DegName + ''' '
fetch next from c into @DegName
end
close c
deallocate c
set @Sql = @Sql + ' from Degrees group by PersonId'
print @sql
exec (@sql)

Erland, i actually learned this dynamic sql from you!

Hope this helps,

Gert-Jan

[Back to original message]