Re: Getting data from multiple rows into one column

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

.

Relevant Pages

  • Sorting & Grouping report
    ... I don't know if this is possible but I have a report grouped on a PersonID # ... there any way to combine the grouping or redo my query to both group on ID ... Gil ... Prev by Date: ...
    (microsoft.public.access.reports)
  • Getting data from multiple rows into one column
    ... PersonID Degree ... 55 Phd ... I need a create a query that will give me output like this: ... Prev by Date: ...
    (comp.databases.ms-sqlserver)
  • Re: Calculate an Average of row entry fields
    ... Ok, I somewhat understand your set-up, but the only thing is that I have more ... SELECT personID, AVG ... If that is not enough to convince you to normalize your design... ... create another query field "TotalPerformanceRating" using the following ...
    (microsoft.public.access.queries)
  • RE: Display fields Limited on Values
    ... you could normalize with a union query. ... SELECT PersonID, Require1 as Met, "Require1" as Requirement ... "Duane Hookom" wrote: ...
    (microsoft.public.access.queries)
  • Re: GROUP BY and performance
    ... If you use the sample I provided and execute the query the result set will ... contain the person information for "Jim Bo" twice since he is a member of ... Essentially I want to GROUP BY personid. ... >> familyid int, ...
    (microsoft.public.sqlserver.programming)