问题描述
我有两个表:标准和服务产品.一个标准可以有多个服务产品.每个标准可以关联不同数量的服务产品.
I have two tables: Standards and Service Offerings. A Standard can have multiple Service Offerings. Each Standard can have a different number of Service Offerings associated to it.
我需要做的是编写一个视图,该视图将返回一些常见数据,然后在一行中列出服务产品.例如:
What I need to be able to do is write a view that will return some common data and then list the service offerings on one line. For example:
我不知道如何写这个.SO 列的数量设置为特定数量(在本例中为 21),因此我们不能超过该数量.
I have no idea how to write this. The number of SO columns is set to a specific number (21 in this case), so we cannot exceed past that.
关于如何解决这个问题的任何想法?
Any ideas on how to approach this?
我开始的地方在下面.当需要在一行上时,它只是为每个服务产品返回多行.
A place I started is below. It just returned multiple rows for each Service Offering, when they need to be on one row.
附加 SQL
这里是我拥有的 SQL,它返回我想要的所有内容,但由于有 11 个服务产品,将返回 11 行.我一直在尝试数据透视表,但似乎无法解决这个问题.有人可以帮忙提供代码示例吗?
So here is the SQL I have that returns everything that I want, but will return 11 rows due to there being 11 Service Offerings. I have been trying the pivot table and can't seem to figure it out with this. Can someone help with a code example?
更新
由于数据库不假设 PIVOT 表(并且无法找出 XML 建议),我不得不执行一些棘手的 SQL 以使其工作.这是我使用的:
Since the database does not suppose PIVOT tables (and couldn't figure out the XML suggestion), I had to do a little tricky SQL to get it to work. Here is what I used:
推荐答案
您可以为此使用 PIVOT 功能.
You can use the PIVOT functionality for this.
查看 http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PIVOTData
Check out http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PIVOTData
您应该使用 FOR XML 和 SplitToColumns 的组合来代替 PIVOT.
Instead of PIVOT, you should use a combination of FOR XML and SplitToColumns.
使用 FOR XML
并将您的产品转出到单列 在 Transact-SQL 中连接行值
Use FOR XML
and pivot out your Offerings to a single column Concatenating Row Values in Transact-SQL
然后使用 CTE 样式函数将单个单元格分解为列,如下所示 http://www.sqlservercentral.com/articles/CTE/67974/
Then use a CTE style function to break down a single cell into columns as shown here http://www.sqlservercentral.com/articles/CTE/67974/
这将为您提供一个以您需要的方式旋转的表格.
This will give you a table pivotted out in the fashion that you need.
然后进行算术运算以获得非空列的计数,最后得到所需的计数.
Then do arithmetic to get a count of non-null columns and you have the count you need at the end.
这篇关于选择返回动态列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!