Posted by Ed Murphy on 02/12/07 19:34

Rex wrote:

> Hi I have a table called "Member" as given below..
>
> familyID memberID firstName
> -------- -------- --------------------
> 0 7 Stuart
> 0 5 Kasey
> 0 1 Sally
> 0 2 Cooper
> 1 9 Rosemary
> 2 3 Lisa
> 2 6 Stephanie
> 3 4 mandy
> 3 8 Fisher
>
>
> I want to create a view, storedProcedure or a Function (whatever is
> possible in SQL Server 2000) that returns data that looks something
> like this:
>
>
> familyID member1 member2 member3 member4 (columns can go to..
> memberN )
> ------- ---------- ---------- --------------
>
> 0 Stuart Kasey Sally Cooper
> 1 Rosemary
> 2 Lisa Stephanie
> 3 Mandy Fisher

Untested:

select familyID,
max(case when memberCount = 1 then firstName) member1,
max(case when memberCount = 2 then firstName) member2,
--- etc.
from (select familyID,
(select count(*)
from Member m2
where m2.familyID = m.familyID
and m2.memberID <= m.memberID) memberCount
from Member m) MemberCounts
group by familyID

[Back to original message]