SQL Server 2000 - 如何在查询的最终结果中轮换连接的结果? [英] SQL Server 2000 - How do I rotate the results of a join in the final results of a query?
问题描述
我的数据库非常复杂,因此我已将问题简化为下表.
My database is quite complex so I've simplified my problem down to the tables below.
TableA
和 TableB
与 TableB
中的 NameID
字段相关.我正在尝试创建一个 SQL 语句来产生所需的结果.我了解 JOIN 及其工作原理,但我无法弄清楚这一点.
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.
对于TableA
中的每个项目,TableB
中的项目永远不会超过 2 个.可能少于 2 个项目.
There will never be more than 2 items in TableB
for each item in TableA
. There could be less than 2 items.
这将用于 SQL Server 2000 服务器.
This will be used on a SQL Server 2000 server.
表A
ID | Name
---+-----
1 | John
2 | Jane
3 | Bob
4 | Doug
表B
ID | NameID | Information
---+--------+------------
1 | 1 | Apples
2 | 1 | Apples
3 | 2 | Pears
4 | 2 | Grapes
5 | 3 | Kiwi
想要的结果
ID | Name | InformationA | InformationB
---+------+--------------+-------------
1 | John | Apples | Apples
2 | Jane | Pears | Grapes
3 | Bob | Kiwi | NULL
4 | Doug | NULL | NULL
推荐答案
(已编辑以给出两列的首选顺序)
(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
这篇关于SQL Server 2000 - 如何在查询的最终结果中轮换连接的结果?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!