Problem with SQL server 2000

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

Any help would be greatly appreciated..Rex wrote:

Quote:

Originally Posted by

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|||Rex (rakeshv01@.gmail.com) writes:

Quote:

Originally Posted by

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 )
--- ---- ---- -----

If you can set an upper limit of, say, 20, members, you can use the
query that Ed posted. If you need the result set to be dynamic, you
will need to use dynamic SQL, and it is not that fun. Or you invest
in the third-party tool RAC, which is good at this. http://www.rac4sql.net.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx