SQL Server 2000 - How do I rotate the results of a join in the final results of a query? (2+ results)

Question

I asked a nearly identical question and got a valid working answer:

Previous Question

There is a minor change here that makes the answer in that question not work in a special case in my database. In all situations expect one there would only be at most 2 possible entries in TableB as I stated this in the previous post. My issue is I have one case where this does not hold true; I need to handle up to 5 items in TableB. - Sorry i would have added it to the original post if I has known.

I do not see how to modify the previous answer for the 2+ items situation I'm running into now as it uses MIN and MAX to solve the original problem.

This will be used on a SQL Server 2000 server.

The order of the Information columns does not matter at all.

TableA

ID | Name
---+------
 1 | John
 2 | Jane
 3 | Bob
 4 | Doug
 5 | Smith

TableB

ID | NameID | Information
---+--------+------------
 1 |    1   | Apples
 2 |    1   | Apples
 3 |    2   | Pears
 4 |    2   | Grapes
 5 |    3   | Kiwi
 6 |    5   | Apples
 7 |    5   | Pears
 8 |    5   | Grapes
 9 |    5   | Kiwi
 10|    5   | Kumkwat

Desired Result

ID | Name | InformationA | InformationB | InformationC | InformationD | InformationE
---+------+--------------+--------------+--------------+--------------+-------------
 1 | John | Apples       | Apples       | NULL         | NULL         | NULL         
 2 | Jane | Pears        | Grapes       | NULL         | NULL         | NULL         
 3 | Bob  | Kiwi         | NULL         | NULL         | NULL         | NULL         
 4 | Doug | NULL         | NULL         | NULL         | NULL         | NULL         
 5 | Smith| Apples       | Pears        | Grapes       | Kiwi         | Kumkwat

Answer 1

This would be much easier/more efficient if row_number were available!

SELECT Id,
       Name,
       MAX (CASE WHEN idx=0 THEN Information END) AS InformationA ,
       MAX (CASE WHEN idx=1 THEN Information END) AS InformationB ,
       MAX (CASE WHEN idx=2 THEN Information END) AS InformationC ,
       MAX (CASE WHEN idx=3 THEN Information END) AS InformationD ,
       MAX (CASE WHEN idx=4 THEN Information END) AS InformationE 
FROM   (SELECT a.Id,
               a.Name,
               b.Information,
               COUNT(b2.Id) idx
        FROM   TableA a
               LEFT JOIN TableB b
                 ON a.Id = b.NameId
               LEFT JOIN TableB b2
                 on b.NameId = b2.NameId
                    and b2.Id < b.Id
        GROUP  BY a.Id,
                  a.Name,
                  b.Id,
                  b.Information) derived
GROUP  BY Id,
          Name