使用 T-SQL 查询具有重复字段的表的高效/高效方法是什么？
我有一个像这样的表（简化的）：
Lender
Id (PK)
AgencyCode1
AgencyCode2
AgencyCode3...
AgencyCode20
Agency
AgencyCode
AgencyName
贷方表是来自报告视图的非规范化数据。我需要在每个贷款人的输出中一行，并且需要将机构代码加入到机构表中以获取每个机构的显示名称。
我在每一行中寻找的结果输出是：
LenderId, AgencyCode1, AgencyName1, ... AgencyCode20, AgencyName20
在查询此类内容时在性能和（开发人员）生产力之间取得最佳平衡的模式是什么？
--编辑，正如我最初指出的那样，机构代码不是主键。
I have a tables (simplified) like this:
Lender
Id (PK)
AgencyCode1
AgencyCode2
AgencyCode3...
AgencyCode20
Agency
AgencyCode
AgencyName
The Lender table is denormalized data coming from a view for reporting. I need one row in my output for each Lender and I need to join the agency codes to an agency table to get the display name for each agency.
The resulting output I am looking for in each row is:
LenderId, AgencyCode1, AgencyName1, ... AgencyCode20, AgencyName20
What is the pattern that strikes the best balance between performance and (developer) productivity to query something like this?
--Edit, Agency Code wasn't a primary key, as I had originally indicated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
呼应@Martin Smith 的评论，unpivot、join 和pivot 可能是最高效、性能最明智的——而且，通过解决如何unpivot 和pivot 的复杂问题，您的开发人员将变得更有经验，从而随着时间的推移变得更有生产力。
或者，如果有 20 个且始终有 20 个非规范化列，您可以只写出 20 个左外连接（假设所有贷款人与 20 个机构不相关）。这是丑陋的代码，并且需要 SQL 处理 20 个连接...但是如果 Agency 表很小（我称 8 页/1 个范围小），那么总处理时间可能相对较短。需要进行测试以确定哪种性能最好。
至于开发人员的整体生产力，请跟踪所有事情花费的时间（包括发布到 SO）。完成后，统计所有内容，并用它来显示开发人员因处理设计不良的表而浪费了多少时间。
Echoing @Martin Smith's comment, unpivot, join, and pivot would probably be the most efficient, performance wise -- and, through working out the intricacies of how to unpivot and pivot, your developers would be more expereienced and thus more productive over time.
Alternatively, if there are 20 and always 20 denormalized columns, you could just write out 20 left outer joins (under the assumption that ever Lender is not related with 20 Agencies). This is ugly code, and would require SQL to process 20 joins... but if the Agency table is small (where I'd call 8 pages/1 extent small), then the overall processing time might be relatively short. Testing would be called for, to determine which performs best.
As regards to developer productivity overall, keep track of how long everything takes (including posting to SO). When it's done, tally everything up, and use it to show how much time was wasted by your developers having to work with poorly designed tables.