Posted by Erland Sommarskog on 02/12/07 22:48

Rex (rakeshv01@gmail.com) writes:
> 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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]