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

Question

My database is quite complex so I've simplified my problem down to the tables below.

TableA and TableB are related by the NameID field in TableB. I am trying to create a SQL statement to produce the desired results. I'm understand JOINs and how they work but I can't fogure this out.

There will never be more than 2 items in TableB for each item in TableA. There could be less than 2 items.

This will be used on a SQL Server 2000 server.

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

"less than 2 items" includes zero? If so you want these shown? — Martin Smith, Jan 12 '11 at 18:40
I changed your sample data for TableB ID 5, NameID from 4 to 3. This change matches the Desired Result. — bobs, Jan 12 '11 at 18:41
@Martin - Yes there could be zero entries in TableB, the result sould include the row. I'll add the example above @bobs - thanks for the fix — Justin808, Jan 12 '11 at 18:44
In the event that you have 2 results do you care whether they go in the A or B column? — Martin Smith, Jan 12 '11 at 18:45
@Martin - Ideally the lower ID would be in the 1st column, but I would be happy with any ordering. — Justin808, Jan 12 '11 at 18:46

Martin Smith · Accepted Answer · 2011-01-12T19:25:27.187

3

(Edited to give the preferred ordering for the 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

edited Jan 12 '11 at 19:25

answered Jan 12 '11 at 18:47

Martin Smith

438,706
87
741
845

Thanks, now to adapt this to my database :) – Justin808 Jan 12 '11 at 18:53

score -1 · Answer 2 · answered Jan 12 '11 at 18:38

-1

I think what you need to do is a pivot. Take a look and see if that suits your needs.

answered Jan 12 '11 at 18:38

Chris B. Behrens

6,255
8
45
71

I don't think PIVOT is available in SQL Server 2000. – bobs Jan 12 '11 at 18:39
This was introduced in SQL2005. (originally had 2008, whoops) – Justin808 Jan 12 '11 at 18:40
Ah, sorry. Here's a StackOverflow question with a reference to that problem, though: http://stackoverflow.com/questions/312861/pivot-using-sql-server-2000. That question in turn references this: http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables. – Chris B. Behrens Jan 12 '11 at 18:43

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

2 Answers2

Linked