“旋转” SQL Server 中的非聚合数据
这将是我在这里发布的第一个问题，因此请原谅董事会礼仪中的任何意外失误。
在我当前的项目中，我采用了一个大型的非规范化表，并将其分成四个单独的规范化表。我接触该板的最终目标是创建一个模仿非标准化表的视图以实现向后兼容性。
为了提供我的场景的简化快照，我想要做的关键在于两个表：
ASSOC_ROLE ASSOCIATE
---------- ----------
assoc_id (fk) assoc_id (pk)
role_id (fk) last_name
org_nbr (fk)
因此，如果我发出以下查询...
SELECT Assoc_Role.org_nbr, Assoc_Role.assoc_id, Associate.last_name, Assoc_Role.role_id
FROM Assoc_Role INNER JOIN
Associate ON Assoc_Role.assoc_id = Associate.assoc_id
WHERE Assoc_Role.org_nbr = '1AA'
...我得到以下结果集
org_nbr assoc_id last_name role_id
------- -------- --------- -------
1AA 1447 Cooper 1
1AA 1448 Collins 3
1AA 1448 Collins 4
1AA 1448 Collins 5
1AA 1449 Lynch 6
最终，我想要的视图构造看起来像这样：
org_nbr role1_ID role1_name role2_ID role2_name role3_ID role3_name role4_ID role4_name role5_ID role5_name role6_ID role6_name
------- -------- ---------- -------- ---------- -------- ---------- -------- ---------- -------- ---------- -------- ----------
1AA 1447 Cooper NULL NULL 1448 Collins 1448 Collins 1448 Collins 1449 Lynch
我最初的想法是尝试使用 PIVOT 命令，但我的理解是 PIVOT 需要某种聚合，这不适合我的场景。我还在 SELECT 子句中使用了 CASE 命令，但它并没有将我的结果集缩减为一条记录。
希望有人能够阐明我如何实现这一目标。如果有人需要更多信息，请告诉我。谢谢！
苏格兰人
this will be the first question I've posted here so pardon any unintended lapses in board etiquette.
In my current project, I've taken a large, non-normalized table and broken it into four separate, normalized tables. My ultimate goal that I'm reaching out to this board for is to create a view which mimics the non-normalized table for backwards compatibility.
To provide a simplified snapshot of my scenario, the crux of what I'm trying to do lies in two tables:
ASSOC_ROLE ASSOCIATE
---------- ----------
assoc_id (fk) assoc_id (pk)
role_id (fk) last_name
org_nbr (fk)
So if I issue the following query...
SELECT Assoc_Role.org_nbr, Assoc_Role.assoc_id, Associate.last_name, Assoc_Role.role_id
FROM Assoc_Role INNER JOIN
Associate ON Assoc_Role.assoc_id = Associate.assoc_id
WHERE Assoc_Role.org_nbr = '1AA'
...I get the following result set
org_nbr assoc_id last_name role_id
------- -------- --------- -------
1AA 1447 Cooper 1
1AA 1448 Collins 3
1AA 1448 Collins 4
1AA 1448 Collins 5
1AA 1449 Lynch 6
Ultimately, the view I would like to construct would look something like this:
org_nbr role1_ID role1_name role2_ID role2_name role3_ID role3_name role4_ID role4_name role5_ID role5_name role6_ID role6_name
------- -------- ---------- -------- ---------- -------- ---------- -------- ---------- -------- ---------- -------- ----------
1AA 1447 Cooper NULL NULL 1448 Collins 1448 Collins 1448 Collins 1449 Lynch
My initial thought was to try to use the PIVOT command, but my understanding is that PIVOT requires some kind of aggregation, and that doesn't fit my scenario. I've also played around with the CASE command in the SELECT clause, but it doesn't flatten my result set down to one record.
Hopefully someone can shed some light on how I can accomplish this. Let me know if anyone needs more info. Thanks!
Scot
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
要获取基本的编号角色数据，我们可以从
BUT 开始，这将为每个
org_nbr提供一个单独的行，用于每个role_id有数据！这不是我们想要的 - 所以我们需要GROUP BY org_nbr。但是接下来我们需要对SELECT列表中的每一列进行GROUP BY或聚合！关键是想出一个聚合函数来安抚 SQL Server 并为我们提供我们想要的结果。在这种情况下，MIN将完成这项工作：输出：
当然，如果最大
role_id增加，这将达不到要求...To get the basic numbered-role data, we might start with
BUT this will give us, for each
org_nbr, a separate row for eachrole_idthat has data! Which is not what we want - so we need toGROUP BY org_nbr. But then we need to eitherGROUP BYor aggregate over every column in theSELECTlist! The trick then is to come up with an aggregate function that will placate SQL Server and give us the results we want. In this case,MINwill do the job:Output:
Of course this will fall short should the maximum
role_idincrease...如果可以的话，我强烈建议使用常规代码（c#、vb 等）进行这种类型的旋转。
SQL Server 中的 PIVOTing 有很多缺点。首先，任何超过 7 或 8 个项目的内容都会大大增加查询所需的时间。其次，它要求您要么执行动态 sql，要么提前知道所有潜在的 id。第三，维护困难。
AakashM的答案中也存在同样的问题。
我们尝试了很多不同的方法来使其在纯 SQL 设置中工作。对于枢轴非常有限的小数据集，它会工作得很好。然而，您已经拥有的角色 ID 的数量超出了这个范围。
相反，只需获取数据并用您最喜欢的语言创建您需要的表。此时，要么将数据放入不同的 SQL 表中，要么将其发送到需要的地方。
If you can, I would highly recommend doing this type of pivoting in regular code (c#, vb, whatever).
PIVOTing in SQL server has a lot of drawbacks. First, anything over 7 or 8 items is going to massively increase the amount of time your queries take. Second, it requires you to either do dynamic sql OR to know all the potential id's ahead of time. Third, it will be difficult to maintain.
The same problems exist in AakashM's answer.
We've tried a lot of different ways to make this work in a pure SQL setting. For small data sets with very limited pivot's it will work just fine. However, the number of Role Id's you already have go beyond it.
Instead, just grab the data and in your favorite language create the table you need. At that point either put the data into a different sql table or send it along to where ever it needs to go.