SQL Server 2000 - How to rotate join results in final query results?

Question

My database is quite complicated, so I simplified my problem to the tables below.

TableAand TableBare connected by a field NameIDin TableB. I am trying to create an SQL statement to get the desired results. I understand EVENTS and how they work, but I cannot guess it.

In TableBfor each element in TableAthere will be no more than two elements. It can be less than two elements.

This will be used on SQL Server 2000.

Tablea

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

Tableb

ID | NameID | Information
---+--------+------------
 1 |    1   | Apples
 2 |    1   | Apples
 3 |    2   | Pears
 4 |    2   | Grapes
 5 |    3   | Kiwi

Desired Result

ID | Name | InformationA | InformationB
---+------+--------------+-------------
 1 | John | Apples       | Apples
 2 | Jane | Pears        | Grapes
 3 | Bob  | Kiwi         | NULL
 4 | Doug | NULL         | NULL

Answer 1

(Edited to give a preferred order for two columns)

SELECT a.Id,
       a.Name,
       STUFF(MIN(STR(b.Id, 10) + b.Information), 1, 10, '') AS InformationA,
       CASE
         WHEN COUNT(b.Id) = 2 THEN STUFF(MAX(STR(b.Id, 10) +
                                   b.Information), 1, 10, '')
       END                                                  AS InformationB
FROM   TableA a
       LEFT JOIN TableB b
         ON a.Id = b.NameId
GROUP  BY a.Id,
          a.Name  

Answer 2

, pivot. , .