我可以使用不带括号的 PIVOT 吗？
PIVOT
(
count(DueCount) FOR dueLibraries.s_folder IN ([Assembly Report-TUL],[Balance-TUL],[BOM-TUL],[Hydrostatic-TUL],[Inspection-TUL],[IOM Manual-TUL],[MTR-TUL],[NDT-TUL],[Performance-TUL],[Inputs - TUL],[Transmitted])
) as MonthlyTally
我宁愿这样：
PIVOT
(
count(DueCount) FOR dueLibraries.s_folder IN (select * from dueLibraries)
) as MonthlyTally
有办法做到这一点吗？
PIVOT
(
count(DueCount) FOR dueLibraries.s_folder IN ([Assembly Report-TUL],[Balance-TUL],[BOM-TUL],[Hydrostatic-TUL],[Inspection-TUL],[IOM Manual-TUL],[MTR-TUL],[NDT-TUL],[Performance-TUL],[Inputs - TUL],[Transmitted])
) as MonthlyTally
I rather just have this:
PIVOT
(
count(DueCount) FOR dueLibraries.s_folder IN (select * from dueLibraries)
) as MonthlyTally
Is there a way to do that?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
看起来，你的问题有两个方面。
首先，第一个代码段的
IN列表中的标识符是分隔标识符。它们必须用方括号分隔，因为它们 不遵守 Transact-SQL 中常规标识符的规则（特别是因为它们包含空格和连字符）。您问题的第二部分是关于用掩码之类的内容替换显式列列表，以使列表动态化。现在，没有可用的语法，您唯一的选择似乎是带有 PIVOT 子句的动态查询。 这是一个示例 如何实施。
Your question is two-fold, as it seems.
First of all, the identifiers in your first snippet's
INlist are delimited identifiers. They have to be delimited with square brackets because they do not obey the rules for regular identifiers in Transact-SQL (particularly, because they include spaces and hyphens).The second part of your question is about replacing the explicit list of columns with something like a mask, to make the list dynamic. Now, there's no available syntax for that, and your only option seems to be a dynamic query with a PIVOT clause. Here's one example of how it can be implemented.